Cacher is the code snippet organizer for pro developers

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

 --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)  
 goNow run the   FIRST   query again.  

 SELECT  
 p.   NAME   ,  
 s.orderqty  
 FROM   production.Product p  
  INNER    JOIN   sales.salesorderdetail s  
  ON   p.ProductID   =   s.ProductIDThe 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.