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.