18

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?

gbn
  • 422,506
  • 82
  • 585
  • 676
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387

3 Answers3

10

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
  • 232,371
  • 49
  • 380
  • 404
  • can we create an index based on a (non-key) column of view? – Anwar Chandra Nov 12 '09 at 11:06
  • @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 Nov 12 '09 at 11:16
  • 2
    @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 Nov 12 '09 at 13:38
  • @Tadmas: Interesting comment! Our tests with indexed views in an OLTP were awful, but maybe it's improved now. – Andomar Nov 12 '09 at 14:04
  • +1 + answer credit - for the most useful explanation given. Thanks! – Shaul Behr Nov 12 '09 at 15:00
  • @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 Nov 12 '09 at 16:34
6

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

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • 2
    and since a indexed view now really is a system-maintained table, one can also create additional non-clustered indices on it – marc_s Nov 12 '09 at 11:18
  • 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 Nov 12 '09 at 13:18
  • Can this impact insert and update performance of the actual tables? Or is the view populated only when queried? – Jack Casas Jul 15 '22 at 09:14
6

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
  • 422,506
  • 82
  • 585
  • 676