Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their thing naturally?
+4
A:
Say you have a view that limits the table to certain rows:
create view dbo.vw_HotProducts
as
select * from dbo.Products where Hot = 1
Now if you create an index on this view, the index only contains hot products. You can compare it to storing the result of the view in a temporary table. This can be very useful for complicated queries with multiple joins; basically their output is cached.
The big disadvantage of indexed views is that they are recreated every time the underlying table data changes. That restricts the use of indexed views to data that does not change often, typically in a data warehouse or business intelligence environment.
Andomar
2009-11-12 11:03:18
can we create an index based on a (non-key) column of view?
Anwar Chandra
2009-11-12 11:06:13
@Q8-coder: an index on a view must be clustered and unique. So you can choose a non-key column, as long as it uniquely identifies a row in the view result
Andomar
2009-11-12 11:16:01
@Andomar: it's not true that an indexed view is recreated on any data change. SQL Server will modify the materialized view to reflect any changes to the base tables. That's why SQL Server places so many restrictions on what views can be materialized: few aggregations, no outer joins, no unions, no self-joins, etc. Granted, it only modifies the data via INSERT/DELETE and not UPDATE. But I have seen indexed views successfully used in an OLTP application joining million-row tables. YMMV
Tadmas
2009-11-12 13:38:16
@Tadmas: Interesting comment! Our tests with indexed views in an OLTP were awful, but maybe it's improved now.
Andomar
2009-11-12 14:04:25
+1 + answer credit - for the most useful explanation given. Thanks!
Shaul
2009-11-12 15:00:16
@Andomar: I saw it used that way with SQL Server 2000, so it's been usable for a while. :) It all depends on the characteristics of your workload. The more complex your views and the higher your throughput, the worse your performance will be. I've seen it cause issues on OLTP systems too. Like any other performance issue, it's all about measuring and balancing resources appropriately.
Tadmas
2009-11-12 16:34:36
+4
A:
See http://msdn.microsoft.com/en-us/library/aa258260%28SQL.80%29.aspx
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
The view is transformed from a proper view into a table. The view definition is used to update this table.
Oracle calls these "materialized views".
S.Lott
2009-11-12 11:04:47
and since a indexed view now really is a system-maintained table, one can also create additional non-clustered indices on it
marc_s
2009-11-12 11:18:44
Looks like the 1st index you create has to be unique and clustered to define the cache table. Additional indexes can be non-unique, like marc_s says!
Andomar
2009-11-12 13:18:07
+2
A:
A view by itself is not real or "persisted", and has no performance benefit. It's simply a macro that's expanded.
Add an index and it physically exists (persisted), so the optimiser will consider using it. It's not a macro then.
I'm sure Oracle calls them "materialised views" which is a better name.
A related FYI: a computed column has a PERSISTED option that does the same thing...
gbn
2009-11-12 13:12:22