Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Indexed Views

By Sarvesh Singh, (first published: 2010/11/16)

A normal view is basically a SELECT statement which has been given a name and stored in the database. It is a virtual table and can be used from other SELECT statements. One or more indexes can be created on a view as long as they meet the requirements mentioned in http://msdn.microsoft.com/en-us/library/ms191432.aspx. When the index is created on the view, the view actually stores the data. If you change the base data the view is automatically updated to reflect these changes.

Indexed views improve read performance and I am going to show this in the demo below.

In SQL Server 2008 Enterprise Edition and Developer Edition, the optimizer can automatically identify and use the indexed view for a query even if there is no mention of it in the query. Consider the below query from Adventureworks database. I am using SQL Server 2008 Developer edition.

Use AdventureWorks
go
select
 p.name,
 s.orderqty
from production.Product p
 inner join sales.salesorderdetail s
 on p.ProductID=s.ProductID

The cost of the query is 1.72.

Let's now create an indexed view. This index must materialize the whole view. This means that the index must be a clustered index and it also needs to be unique. For this I am going to add the column SalesOrderDetailID to the indexed view.

create view vProductSold
with schemabinding
as
select
 p.name,
 s.orderqty,
 s.salesorderdetailid
from production.Product p
 inner join sales.salesorderdetail s
 on p.ProductID=s.ProductID
go
create unique clustered index vidx_ProductSold
on vProductSold (salesorderdetailid)
go

Now run the first query again.

select
 p.name,
 s.orderqty
from production.Product p
 inner join sales.salesorderdetail s
 on p.ProductID=s.ProductID

The execution plan after the indexed view is created is shown below.

The cost of the Query is dropped to 0.79 from 1.72. This time the optimizer is using the indexed view eventhough it is not referenced in the query.

If you are not using SQL Server 2008 Enterprise or Developer Edition then you will need to reference the view directly and add the hint WITH(NOEXPAND). You can find more information about this hint in Books Online.

Conclusion

Indexed views can improve performance significantly if you have queries that combine large volumes of data with aggregates. It should not be created against tables where lot of modifications or changes are happening. This is because indexed views have to be maintained when there are changes happening to base data.

Total article views: 23996 | Views in the last 30 days: 17
 
Related Articles
FORUM

Disabling index by using select query

Disabling index by using select query

FORUM

Select query

Select query

FORUM

Creating an index

Creating an index

FORUM

Tips on creating indexes

Tips on creating indexes

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones