Indexed Views SQL 2008

Reff: http://sarveshsingh.com/2011/06/20/indexed-views.aspx

Indexed Views

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.

Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s