First published on MSDN on Sep 04, 2016
Are indexed views used automatically in SQL Azure without "
"?How does it work?and How the query optimizer decides to use it or not ?
The query optimizer uses indexed views to speed up the query execution. Which means SQL Server's query optimizer realizes the precomputed sums of all the Quantity values for each value are already available in the index for the view. The optimizer will evaluate the cost of using that indexed views in processing the query. But just because you have an indexed views doesn't mean the query optimizer will always choose it for the query's execution plan.
In fact, even if you reference the indexed views directly in the
clause, the optimizer might decide to directly access the base table instead,But
The optimizer decides whether the view's query tree matches the submitted query's query tree, then determines which is more cost-effective: using the indexed view or the base table. Optimizer might decide that using the base table is cheap enough and won't even bother determining a cost for using the indexed views.
So,we can’t always predict what the query optimizer will do!
Query optimizer will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used.
if you are testing a query ,and your data is
and the cost for selecting directly from the base tables maybe is cheaper than the estimated cost from the view,then indexed views
won't be used
. Taking in consideration that you can force the optimizer to use the indexed views by using the hint