This statistic is built because the expected number and distribution of Customer IDs might be important, for example in choosing an aggregation strategy.
We can see the new statistic using Management Studio Object Explorer: Double-clicking the statistics object confirms it was built from the Customer ID column on the view (not a base table): Still using Standard Edition, we now drop and recreate the indexed view (which also drops the view statistics) and execute the query again, this time with the , the resulting query plan operates on the base tables rather than the view directly: The warning triangle on the root operator in the plan above is alerting us to a potentially useful index on the Sales Order Detail table, which is not important for our present purposes.
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.
This compilation does not create any statistics on the indexed view.
The only statistic on the view after query compilation is the one associated with the clustered index: The Plan Tree view for the query shows that cardinality estimation is correct for the two table scans and the join, but quite a bit worse for the other plan operators: Using the indexed view with a hint resulted in more accurate estimates for our test query because better quality information was available from statistics on the view – in particular, the statistics associated with the view index.
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.
In these circumstances, the query hint hint disables rules in the optimizer that can match parts of the expanded tree back to indexed views.Indexed views can be created in any edition of SQL Server, but there are a number of behaviours to be aware of if you want to make the most of them.SQL Server can create statistics automatically to assist with cardinality estimation and cost-based decision-making during query optimization.As a general rule, the accuracy of statistical information degrades quite quickly as it passes through and is modified by query plan operators.Simple joins are often not too bad in this regard, but information about the result of an aggregation is often no better than an educated guess.In the absence of either hint, SQL Server first expands a view to its base table definition, then later considers matching back to indexed views.A better name for the The query hint results in the same execution plan and estimates seen when we were using Standard Edition and the same base-table-only query: Even in Enterprise Edition, non-index view statistics are still only created if the hint is used.This feature works with indexed views as well as base tables, but only if the view is explicitly named in the query and the hint is specified.(There is always a statistics object associated with each index on a view, it is the automatic generation and maintenance of statistics not associated with an index that we are talking about here.) If you are used to working with non-Enterprise editions of SQL Server, you may never have noticed this behaviour before.The second thing of interest is that the cardinality estimates appear to be worse than any case we have encountered so far, including the Standard Edition examples.It is initially difficult to see where the cardinality estimate for the View Clustered Index Seek (11,267) came from.