Click or drag to resize

SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view's index

The topic describes the SA0164 analysis rule.

Message

Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view's index

Description

The rule checks for use of indexed views in SQL Server edition different than Enterprise or Developer, without specifying WITH(NOEXPAND) query hint.

SQL Server allows indexed views to be created in any edition, but it is just in Enterprise edition where the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used.

Note Note

NOTE: SQL Server Developer edition also supports automatic use of indexed view by query optimizer, as the Developer edition supports all the features of the Enterprise edition.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
SuppressInEditions

Parameter specifies a comma separated list of SQL Sever editions, for which the rule not to be applied.

Enterprise

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Categories

Performance Rules

Additional Information
Example Test Script
SQL
 1-- The SELECT statement will cause rule violation there are several views were joined in this query. 
 2select e.*
 3from HumanResources.vEmployee as e
 4inner join HumanResources.vEmployeeDepartment as ed on ed.EmployeeID = e.EmployeeID,
 5HumanResources.vEmployeeDepartmentHistory as edh,
 6Employee as a;
 7
 8--  Rows are selected form the HumanResources.vEmployee view only.
 9
10select  e.*
11from HumanResources.vEmployee as e with (noexpand); 
12
13
14--  Rows are selected form the HumanResources.vEmployee view only.
15
16select e.*
17from HumanResources.vEmployee as e (noexpand);                         
18
19
20--  Rows are selected form the HumanResources.vEmployee view only.
21
22select e.*
23from HumanResources.vEmployee as e (noexpand); 
24
25select * from Person.vStateProvinceCountryRegion
26select * from Production.vProductAndDescription  with (noexpand)
27
28select * from Person.vStateProvinceCountryRegion e with (noexpand)
29select * from Production.vProductAndDescription  
30
31select e.*
32from Production.vProductAndDescription (noexpand); 
33
34select e.*
35from Production.vProductAndDescription as e (noexpand); 
36
37
38select e.*
39from Production.vProductAndDescription (NOEXPAND); 
40
41select * from Person.vStateProvinceCountryRegion e with (nolock)

Analysis Results

 MessageLineColumn
1SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view's index.2521
2SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view's index.2925
3SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view's index.4121
See Also

Other Resources