How view expansion and matching work
The documentation for the
NOEXPAND table hint is confusingly worded.
The fact of the matter is that references to indexed views are always expanded into the view definition early on in the query compilation process, unless this hint is provided.
On some editions (Enterprise, Developer, Evaluation, Azure SQL Database) the optimizer may match the expanded definition, or any other part of a potential plan tree, to one or more indexed views later in the process.
This might sound counter-productive and redundant, but it has important benefits. The expanded form may admit simplifications or other optimization opportunities that are simply not possible or evident with the indexed view reference itself. In addition, the ability to match arbitrary parts of plan alternatives to indexed views can be a very powerful feature.
When this works well, the database administrator can provide a selection of indexed views that benefit a wide range of queries, without the query writer needing to specify those views explicitly. Moreover, the optimizer can explore plan alternatives (e.g. introducing or splitting aggregates) that match one or more indexed views, where the original query would not.
When to use
You can use the
NOEXPAND hint in any edition. You should use it when you are certain that accessing the indexed view is the best possible option. Be aware that you are taking choices away from the query optimizer by doing this.
You must use
NOEXPAND in any edition not mentioned in the prior section to access the indexed view directly (else it will be expanded).
You should also use
NOEXPAND — even in Enterprise-equivalent or Azure SQL Database — if you want SQL Server to automatically create statistics on the indexed view for better cardinality estimates. See my article for more details.
When not to use
You might choose not to use this hint when:
- You create & maintain indexed view statistics manually; and
- You trust SQL Server to decide whether to use the indexed view or not, based on its cost estimates.
This option only applies to Enterprise-equivalent and Azure SQL Database, because only those versions are capable of matching plan sections to indexed views for you.
In most cases, people write queries with explicit indexed view references because those objects are designed to provide optimal performance. It makes sense to use
NOEXPAND in those cases for reliability. You wouldn't want SQL Server to mistakenly expand the view just because a cardinality mis-estimate makes that option seem cheaper, when it is not.
So saying, there can be times when allowing SQL Server to expand the view at its discretion provides a better outcome. This typically occurs when the base tables provide indexes the indexed view does not, or other table-level constraints and query information means the expanded query can be greatly simplified.
There is an inherent bias in the question when it says, "if the view is designed well". This might be taken to mean that the indexed view is necessarily optimal for all designed cases. People create all sorts of indexed views, not all of which are so clear-cut winners. They can still be useful in some cases — perhaps to avoid several joins — without being optimal for all cases.