How do indexes work on views?

Question

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?

Answer 1

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.

Answer 2

See http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).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".

Answer 3

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...