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?

Ithunn

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.

Xylene

can we create an index based on a (non-key) column of view? – Emblaze

@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 – Xylene

@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 – Lisandralisbeth

@Tadmas: Interesting comment! Our tests with indexed views in an OLTP were awful, but maybe it's improved now. – Xylene

+1 + answer credit - for the most useful explanation given. Thanks! – Ithunn

@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. – Lisandralisbeth

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

Thales

and since a indexed view now really is a system-maintained table, one can also create additional non-clustered indices on it – Scutch

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! – Xylene

Can this impact insert and update performance of the actual tables? Or is the view populated only when queried? – Osmo

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

Carlinecarling