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

Introduction

Improper use of indexed views in non-Enterprise or non-Developer editions of SQL Server can result in inefficient query performance.

Description

In SQL Server, an indexed view can optimize query performance by storing precomputed results as an index. However, in editions other than Enterprise or Developer, the query optimizer will not automatically consider these indexed views unless explicitly instructed using the WITH(NOEXPAND) hint.

For example:

-- Example of a problematic query in SQL Server Standard edition
SELECT * FROM MyIndexedView;
-- Without using WITH(NOEXPAND)

This query might not leverage the performance benefits of an indexed view because the optimizer in non-Enterprise editions overlooks the index unless WITH(NOEXPAND) is specified. As a result:

Query performance may degrade because the optimizer processes the view as if it were a regular table, re-evaluating the computations unnecessarily.
It negates the benefits of having an indexed view, particularly in high read scenarios where performance gains are critical.

How to fix

This section provides guidance on optimizing the performance of queries using indexed views in SQL Server editions that are not Enterprise or Developer by employing the WITH(NOEXPAND) hint.

Follow these steps to address the issue:

1.Identify queries that reference indexed views in SQL Server Standard or Datacenter editions. These queries may not automatically benefit from the indexed view without further action.

2.Modify the query to include the WITH(NOEXPAND) hint. This ensures the query optimizer uses the index created for the view, enhancing performance.

3.Review and test the query to confirm performance improvements, especially in scenarios with high read operations, where the indexed view should offer significant benefits.

For example:

-- Example of a corrected query in SQL Server Standard edition
SELECT * FROM MyIndexedView WITH (NOEXPAND);

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.

Additional Information

Resolving Indexes on Views

Example Test SQL

SELECT  *
FROM  Person.vStateProvinceCountryRegion

SELECT  *
FROM  Production.vProductAndDescription  WITH ( NOEXPAND )

SELECT  *
FROM  Person.vStateProvinceCountryRegion AS e WITH ( NOEXPAND )

SELECT  *
FROM  Production.vProductAndDescription

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.	2	13
2	SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index.	11	17

Analysis Rules