Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an indexed view is treated by SQL Server?
Sql-server – On a single table, how is an indexed view different from a non clustered index
materialized-viewsql server
Related Question
- Sql-server – Unique clustered index on indexed view fails because of aggregates
- Sql-server – what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view
- Sql-server – Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table
- Sql-server – Indexed View over multiple partitioned tables and ALTER TABLE SWITCH
- Sql-server – MSSQL – How to support aggregate pushdown on indexed view with SUM(decimal)
- Sql-server – Indexed view referencing objects on two different schemas
- Sql-server – Query FROM Indexed View queries underlying table in Execution plan
Best Answer
See this article: Resolving Indexes on Views for detail on how the query optimizer treats indexed views.
The normal use case for indexed views is aggregations (eg
COUNT_BIG,SUMwithGROUP BYetc) or complex formulas / calculations you want to keep in one place but do not want to use a computed column. Basically the indexed view would look different to the base table, or to put it another way, you can do things with indexed views you can't do with a base table.If you created an indexed view that is simply the base table and a unique clustered index (which is compulsory for an indexed view), then conceptually it is a duplicate of the base table and you may have missed the point of indexed views as described above. You have also incurred a considerable index maintenance overhead for no reason.
Indexed View matching, the feature by which the Query Optimizer will use an indexed view even if it is not referenced in the query, (provided that view can 'answer the question') is harder than normal index selection so you also incur a risk of this not happening. Although in your case where the view is a duplicate of the table it is irrelevant. Indexed view matching is also an Enterprise edition feature, although you can override this with the
NOEXPANDhint in Standard edition.None of this is a concern for a conventional non-clustered index which may or may not be used depending on a large number of factors, although selection is probably more straightforward than for indexed views.
In summary, an indexed view implemented as you describe is simply an unnecessary duplicate of the base table. I would be very careful about implementing them, unless there was a specific use case/example where it was shown to be significantly faster using an indexed view, and/or you can live with the overhead having trialled it extensively with your other workloads.
Indexed views do have their uses and there are some interesting discussions on them recently here, here and here.
HTH