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.