Why would you want to put an index on a view?

Question

Microsoft SQL Server allows you to add an index to a view, but why would you want to do this?

My understanding is that a view is really just a subquery, i.e., if I say SELECT * FROM myView, i'm really saying SELECT * FROM (myView's Query)

It seems like the indexes on the underlying tables would be the ones that matter the most. So why would you want a separate index on the view?

Answer 1

If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance.

Essentially, the database engine is maintaining a "solved" version of the query (or, rather, the index of the query) as you update the underlying tables, then using that solved version rather than the original tables when possible.

Here is a good article in Database Journal.

Answer 2

Microsoft SQL Server allows you to add an index to a view, but why would you want to do this?

To speed up the queries.

My understanding is that a view is really just a subquery, i.e., if I say SELECT * FROM myView, i'm really saying SELECT * FROM (myView's Query)

Not always.

By creating a clustered index on a view, you materialize the view, and updates to the underlying tables physically update the view. The queries against this view may or may not access the underlying tables.

Not all views can be indexed.

For instance, if you are using GROUP BY in a view, for it to be indexable it should contain a COUNT_BIG and all aggregate functions in it should distribute over UNION ALL (only SUM and COUNT_BIG actually are). This is required for the index to be maintainable and the update to the underlying tables could update the view in a timely fashion.