Indexed Views | Microsoft SQL Server 2000 Unleashed (2nd Edition)

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:

Create indexes on views where the underlying table data is relatively static.
Create indexed views that will be used by several queries.
Keep the indexes small. Just like with table indexes, a smaller index allows SQL Server to access the data more efficiently .
Create indexed views that will be significantly smaller than the underlying table(s). An indexed view might not provide significant performance gains if its size is similar to the size of the original table.
You will need to specify the NOEXPAND hint in editions of SQL Server other than the Developer or Enterprise Editions of SQL Server, or the indexed view will not be used to optimize the query.