Create Indexed View in SQL
--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.