Ms sql view not updating
The query below expresses the same logical requirement, but does not reference the view: This query produces the following execution plan: This plan features one less aggregation operation than before.
When view expansion was used, the query optimizer was unfortunately unable to remove a redundant aggregation operation, resulting in a less efficient execution plan.
The final cardinality estimate for the new query is also slightly better than when the indexed view was referenced without (repeated below for convenience): On an Enterprise Edition instance, the query optimizer may be able to use an indexed view even if the query does not mention the view explicitly.
If the optimizer is able to match part of the query tree to an indexed view, it can choose to do so, based on its estimation of the costs of using the view or not.
Starting with the rarer of the possibilities, there may be occasions where a query references an indexed view, but a better plan would be obtained by accessing the base tables instead.
SQL Server can create statistics automatically to assist with cardinality estimation and cost-based decision-making during query optimization.
The view-matching logic is reasonably clever, but it does have limits that are pretty easy to hit in practice.
Even where view matching is successful, the optimizer can still be misled by inaccurate cost estimations.
Providing the query optimizer with more accurate information using statistics on indexed views can be a useful technique to increase plan quality and robustness.
The query plan shown above (Standard Edition, without ) is actually less optimal than if we had written the query against the base tables ourselves, rather than allowing the query optimizer to expand the view.
Lower editions of SQL Server require the is specified, automatic statistics are created on indexed views exactly as happens with ordinary tables.