0

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?

1

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, SUM with GROUP BY etc) 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 NOEXPAND hint 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.