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.

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.

How to fix

If the SQL Server edition that will be used is Standard edition or the Datacenter edition, use the NOEXPAND query hint to enable the query optimizer to use the index of the view.

Otherwise the rule can be suppressed.

Scope

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

Parameters
Name Description Default 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.

Effort To Fix
3 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information
Example Test SQL
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
  Message Line Column
1 SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index. 25 21
2 SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index. 29 25
3 SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index. 41 21
See Also

Other Resources