Using Indexes on Views
The Microsoft® SQL Server™ 2000 query optimizer determines whether a given query will benefit from using any indexes defined in the database. This includes both indexed views and indexes on base tables. The SQL Server query optimizer uses an indexed view when these conditions are met:
- These session options are set to ON:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIERS
- ANSI_NULLS
- The NUMERIC_ROUNDABORT session option is set to OFF.
- The optimizer finds a match between the view index columns and elements in the query, such as:
- Search condition predicates in the WHERE clause.
- Join operations.
- Aggregate functions.
- Search condition predicates in the WHERE clause.
- The estimated cost for using the index has the lowest cost of any access mechanisms considered by the optimizer.
Other than the requirements for the SET options, these are the same rules the optimizer uses to determine if an index covers a query. Nothing has to be specified in the query to make use of an indexed view.
A query also does not have to specifically reference an indexed view in the FROM clause for the optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the optimizer estimates that using the indexed view provides the lowest cost access mechanism, the optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. The optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.
You can prevent view indexes from being used for a query by using the EXPAND VIEWS option. You can use the NOEXPAND view hint to force the use of an index for an indexed view specified in the FROM clause of a query. It is usually best, however, to let the optimizer dynamically determine the best access methods to use for each individual query. Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown they significantly improve performance.
The EXPAND VIEWS option specifies that the optimizer not use any view indexes for the entire query.
- The optimizer does not use any indexed views. The optimizer ignores all view indexes when estimating the low-cost method for covering columns referenced in the query.
- The optimizer treats an indexed view referenced in the FROM clause as a standard view. The optimizer incorporates the logic of the view into the query execution plan and dynamically builds the result set from the base tables. The optimizer ignores indexes defined on the view.
When NOEXPAND is specified for a view, the optimizer considers the use of any indexes defined on the view. NOEXPAND specified with the optional INDEX() clause forces the optimizer to use the specified indexes. NOEXPAND can be specified only for an indexed view and cannot be specified for a view that has not been indexed.