Professional SQL Server™ 2005 Programming

9.9. Indexed (Materialized) Views

In SQL Server 2000, this one was supported only in the Enterprise Edition (okay, the Developer and Evaluation Editions also supported it, but you aren't allowed to use test and development editions in production systems). It is, however, supported in all editions of SQL Server 2005.

When a view is referred to, the logic in the query that makes up the view is essentially incorporated into the calling query. Unfortunately, this means that the calling query just gets that much more complex. The extra overhead of figuring out the impact of the view (and what data it represents) on the fly can actually get very high. What's more, you're often adding additional joins into your query in the form of the tables that are joined in the view. Indexed views give you a way of taking care of some of this impact before the query is ever run.

An indexed view is essentially a view that has had a set of unique values "materialized" into the form of a clustered index. The advantage of this is that it provides a very quick lookup in terms of pulling the information behind a view together. After the first index (which must be a clustered index against a unique set of values), SQL Server can also build additional indexes on the view using the cluster key from the first index as a reference point. That said, nothing comes for free — there are some restrictions about when you can and can't build indexes on views (I hope you're ready for this one — it's an awfully long ...