sql - 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?

question from:https://stackoverflow.com/questions/4737490/why-would-you-want-to-put-an-index-on-a-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.