How do indexes work on views?

sql sql-server indexing views indexed-view
Shaul Behr picture Shaul Behr · Nov 12, 2009 · Viewed 28.8k times · Source

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

Andomar picture Andomar · Nov 12, 2009

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.

Related questions

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
Rebuild all indexes in a Database

I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was wondering if …

Read More
sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? …

Read More