Resolving Indexes on Views
As with any index, SQL Server chooses to use an indexed view in its query plan only if the query optimizer determines it is beneficial to do so.
Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
The SQL Server query optimizer uses an indexed view when the following conditions are met:
These session options are set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
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 the following:
Search condition predicates in the WHERE clause
Join operations
Aggregate functions
GROUP BY clauses
Table references
The estimated cost for using the index has the lowest cost of any access mechanisms considered by the query optimizer.
Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.
Note
The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.
Other than the requirements for the SET options and table hints, these are the same rules that the query optimizer uses to determine whether a table index covers a query. Nothing else has to be specified in the query for an indexed view to be used.
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.
The query optimizer treats an indexed view referenced in the FROM clause as a standard view. The query optimizer expands the definition of the view into the query at the start of the optimization process. Then, indexed view matching is performed. The indexed view may be used in the final execution plan selected by the optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. The optimizer chooses the lowest-cost alternative.
Using Hints with Indexed Views
You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. However, you should let the query optimizer dynamically determine the best access methods to use for each query. Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.
The EXPAND VIEWS option specifies that the query optimizer not use any view indexes for the whole query.
When NOEXPAND is specified for a view, the query optimizer considers using 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.
When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. Most of the time, these hints will match each other, because they are being inherited directly from the view. However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.
If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. To specify use of a particular index, use NOEXPAND.
Generally, when the query optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. If the query optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. For more information, see View Resolution. This propagation does not apply to join hints. They are applied only in their original position in the query. Join hints are not considered by the query optimizer when matching queries to indexed views. If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.
Hints are not allowed in the definitions of indexed views in SQL Server 2008. In compatibility mode 80 and higher, SQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.