SQL Server Indexed views used instead of tables

Question

I am a little bit confused about using indexed views in SQL Server 2016.

Here is my issue. If I have a fact table with a lot of columns and I create an indexed view named IV_Sales as

select 
    year, 
    customer, 
    sum(sales) 
from F_Sales 
group by year, customer

I would aggregate all sales for year and customer.

After that, when a user runs a query from the F_sales like

Select 
    year, customer, 
    sum(sales) 
from F_sales 
group by year, customer

will the Optimizer (in SQL Server Enterprise Edition) automatically use the indexed view IV_sales instead of table scan of F_sales? I have the Standard Edition and when I add

Select 
    year, 
    customer, 
    sum(sales) 
from F_sales WITH (NOEXPAND) 
group by year, customer

I get an error since there is no clustered index like the one I created on the indexed view. Is there a way to force using index views instead of the table in Standard Edition?

My real world issue is that I have a Cognos Framework model pointing to the table F_sales and when a report is executed using Year, customer and sum of sales for performance reasons I want it to use the indexed view automatically instead of the table.

I hope I'm being clear about my issue. Many thanks in advance.

I would be *very* surprised if SQL Server would use an indexed view if the view were not mentioned in a query. — Gordon Linoff, Apr 11 '17 at 11:56
There is no way to *force* SQL Server to use an indexed view, and in fact, while Enterprise Edition can in *theory* use an indexed view even when it's not mentioned in a query, in *practice* it's bad at this -- so bad, in fact, that you often have to specify `NOEXPAND` even when querying the view just to force it to consider indexes on the view! If you still want to use it, this feature (indexed view matching) is available in Standard Edition from SP1 onwards, but I've never had much luck with it. — Jeroen Mostert, Apr 11 '17 at 12:24

score 0 · Answer 1 · answered Sep 01 '17 at 01:40

If you have a performance issue, Indexed views are probably the last thing you want to try.

You should exhaust all other avenues, like standard indexes first.

For example if you know for sure that you are doing a table scan, the simple solution is to add a non clustered index to satisfy the query so it does an index scan or seek instead. If it still doesn't use this, you need to continue your performance tuning, and work out why it isn't (non sargable expressions? stale statistics?)

Your indexed view will automatically be used (without explicit mention of the indexed view) in a very limited number of cases. You'll see it in the query plan.

If your query very closely matches the index view definition, it will use your indexed view.

Make a very small change to your SQL, (like joining to another table) and it won't throw an error, it will just fall back to not using the indexed view.

Automatic SQL writing tools like Cognos will very quickly make the SQL unrecognisable to the query planner and therefore not use the indexed view.

This is all very easily verifiable if you just crack open SSMS and do some experiments.

So in short: start your optmisation with standard indexes, filtered indexes, even column store indexes (which are particularly good for fact tables or so I hear)

SQL Server Indexed views used instead of tables

1 Answers1