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

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?

---

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.

---

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.

---

the following link provides better worded information than i can say especially in the section under performance increases. Hope it helps

http://technet.microsoft.com/en-us/library/cc917715.aspx

---

You create an index on a view for the same reason as on a base table: to improve the performance of queries against that view. Another reason for doing it is to implement some uniqueness constraint you can't implement against base tables. SQL Server unfortunately doesn't allow constraints to be created on views.