I have a view that collects data from several tables. While there are no indexes on the view itself anything that uses the view seems to benefit from the underlying tables having indexes. Are these being used automatically? If they are then what is the point of creating indexes on your views? Any recommended articles on this subject would be welcomed.
+3
A:
Yes, the underlying table indexes are used automatically - a view just pulls the data from the underlying tables after all.
With regards to the benefits of creating indexes on a view, see this MS Technet article. Small excerpt:
Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:
- Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
- Tables can be prejoined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
AdaTheDev
2009-12-17 18:56:48
So what is the point of having indexed views? To improve insert/update performance on the table it self while still having indexes of the data you need?
Abe Miessler
2009-12-17 18:58:05
The point of having an indexed view is to make your view lookups EVEN FASTER, especially with regards to computed fields.
Mark Canlas
2009-12-17 18:59:57
To put it another way, an index is a small summary of a larger body of data, your tables. When you make an indexed view, you aren't even wasting your time with large tables of raw and their indexes; you're getting exactly what you want and quickly, no more, no less.
Mark Canlas
2009-12-17 19:02:04
Indexed views (or "materialized views") do *NOT* help with inserts and updates. They are, essentially, a copy of the data that's stored in the underlying tables, so updating the data would require modifications in two locations (the table and the indexed view). While they can be useful to improve SELECT performance, it completely depends upon your data and data retrieval requirements. I have only used them when I *knew* that I would benefit from them.
Philip Kelley
2009-12-17 19:03:30
+1
A:
The query optimizer rewrites the query and "flattens" the use of sub-queries (which a view really is). So underlying indexes will be used.
Alex Brasetvik
2009-12-17 18:57:13