Resolving Indexes on Views

Resolving 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
• The NUMERIC_ROUNDABORT session option is set to OFF.
  
  
• The query 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.
• The estimated cost for using the index has the lowest cost of any access mechanisms considered by the query optimizer.

Other than the requirements for the SET options, these are the same rules the query optimizer uses to determine if a table index covers a query. Nothing has to be specified in the query to make use of an indexed view.

A query does not have to explicitly reference an indexed view in the FROM clause for the query 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 query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. The query 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 recommended, however, to let the query 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 improve performance significantly.

The EXPAND VIEWS option specifies that the query optimizer not use any view indexes for the entire query.

• The query optimizer does not use any indexed views unless the view is specified in the FROM clause. The query optimizer ignores all view indexes when estimating the low-cost method for covering columns referenced in the query.
  
  
• The query optimizer treats an indexed view referenced in the FROM clause as a standard view. The query optimizer incorporates the logic of the view into the query execution plan and dynamically builds the result set from the base tables. The query optimizer ignores indexes defined on the view.

When NOEXPAND is specified for a view, the query optimizer considers the use of any indexes defined on the view. NOEXPAND specified with the optional INDEX() clause forces the query optimizer to use the specified indexes. NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.