When are Indexed Views used?

Question

Post reply

When are Indexed Views used?

OrbMan

SSC Veteran

Points: 299
More actions
July 23, 2004 at 6:09 am

#108662

Assuming you are not referencing the indexed view directly in your query, under what conditions will SQL Server choose to use the indexed view? And is there a way to explicitly tell it to not use any indexed views?
I want the ability to observe the performance difference at the application level by essentially toggling on and off indexed views.
July 26, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 1

You could create the same view (non indexed) by a using a different object name.

Example

View1Indexed

View1NonIndexed

Then call in the application code accordingly.

Keep it simple, huh?

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 2

On the other hand . . .

ALTER VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]

[ WITH < view_attribute > [ ,...n ] ]

AS

select_statement

[ WITH CHECK OPTION ]

< view_attribute > ::=

{ ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

OrbMan SSC Veteran Points: 299 More actions · Answer 3

The issue is that SQL Server can use indexed views even when they are not referenced explicitly. I am not certain of the exact conditions under which this occurs.

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 4

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.

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 5

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
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.
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.

OrbMan SSC Veteran Points: 299 More actions · Answer 6

Yup, everything I need is in there. I should have re-read the books...thanks!