SQL SERVER – Using NOEXPAND with Indexed View

NOEXPAND treats an indexed view as stored indexed data instead of expanding its base tables. Customer results have differed.

-- Compare on an existing, valid indexed view with required SET options:
-- SELECT ColumnName FROM dbo.YourIndexedView;
-- SELECT ColumnName FROM dbo.YourIndexedView WITH (NOEXPAND);

An ordinary view stores a definition rather than a materialized result. A unique clustered index stores an indexed view result and maintains it with base-data changes. NOEXPAND applies to that indexed view. It does not apply to arbitrary ordinary views.

The optimizer can otherwise choose base-table access. Automatic matching and SET requirements depend on product and edition. NOEXPAND also affects automatic statistics behavior for the indexed view. Test those conditions rather than promising improvement.

One customer benefited from the hint; other cases benefited from removing it. Compare plans, correct results, reads, CPU and duration. Include view maintenance cost. My procedure-and-function preference does not establish universal superiority.

The linked view-limitations resources provide related examples. Both customer outcomes matter. Use the measured access choice rather than a blanket recommendation.

Reference: NOEXPAND requirements and statistics behavior.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
• SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
• SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
• SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
• SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
• SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
• SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
• SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
• SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
• SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
• SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11
• SQL SERVER – Index Created on View not Used Often – Limitation of the View 12
• SQL in Sixty Seconds
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A view hint is not a guaranteed improvement, it is an access choice to compare with the alternatives.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.