As discussed in Chapter 27, "Creating and Managing Views in SQL Server," SQL Server 2000 allows you to create indexed views. An indexed view is any view that has a clustered index defined on it. When a CREATE INDEX statement is executed on a view, the resultset for the view is materialized and stored in the database with the same structure as a table with a clustered index. Changes made to the data in the underlying tables of the view will be automatically reflected in the view the same way any changes to a table are reflected in its indexes. In addition to a clustered index, you can create additional nonclustered indexes on indexed views to provide additional query performance. Additional indexes on views might provide more options for the query optimizer to choose from during the optimization process.
In the Developer and Enterprise Editions of SQL Server 2000, when an indexed view exists on a table and you access the view directly within your query, the optimizer will automatically consider using the index on the view to improve query performance, just as an index on a table is used to improve performance. The query optimizer will also consider using the indexed view even for queries that do not directly name the view in the FROM clause. In other words, when a query might benefit from using the indexed view, the query optimizer can use the indexed view to satisfy the query in place of an existing index on the table itself. (For more information on how indexed views are used in query plans, see Chapter 35).
It is important to note that while indexed views can be created in all editions of SQL Server 2000, only the Developer and Enteprise editions will use indexed views automatically to optimize queries. In the other editions, indexed views will not be used to improve query performance unless the view is explicitly specified in the query and the NOEXPAND optimizer hint is specified as well. Without the NOEXPAND hint, SQL Server will expand the view to its underlying base tables and optimize based upon the table indexes. The following example shows the use of the NOEXPAND option to force SQL Server to use the indexed view specified in the query:
select * from sales_Qty_Rollup WITH (NOEXPAND) where stor_id between 'B914' and 'B999'SET ARITHABORT ON
Indexed views do add overhead and can be more complex for SQL Server to maintain over time than normal indexes. Each time an underlying table of a view is modified, SQL Server has to update the view resultset and potentially the index on that view. The scope of a view's index can be larger than that of any single table's index, especially if the view is defined on several large tables. The overhead associated with maintaining a view and its index during updates can negate any benefit that queries gain from the indexed view. Because of this additional maintenance overhead, create indexes only on those views where the advantage provided by the improved speed in retrieving the results outweighs the increased maintenance overhead.
Guidelines for Indexed Views
Consider these guidelines when you design indexed views: