An indexed view is a view that has a unique clustered index created on it. Normally views do not exist on disk as rows. That changes for indexed views, which exist in the database as rows that realize the view. There can also be non-clustered indexes on the view so long as it has the unique clustered index.
- The disk space taken up by the view
- The cost of maintaining the view on disk as the base tables are modified.
- View definition must always return the same results from the same underlying data.
- Views cannot use non-deterministic functions.
- The first index on a View must be a clustered, UNIQUE index.
- If you use Group By, you must include the new COUNT_BIG(*) in the select list.
- View definition cannot contain the following
- Text, ntext or image columns
- MIN, MAX, COUNT, STDEV, VARIANCE, AVG
- SUM on a nullable expression
- A derived table
- Rowset function
- Another view
- Subqueries, outer joins, self joins
- Full-text predicates like CONTAIN or FREETEXT
- COMPUTE or COMPUTE BY
- Cannot include order by in view definition
- Make sure that session properties are properly set.
- Create a deterministic view with new SCHEMABINDING syntax.
- Create unique Clustered Index.
- Optionally, create additional nonclustered indexes.
Follow below steps to create a Indexed View. This example is based on the AdventureWorks sample database.
1. Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
2. Create Indexed view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL DROP VIEW Sales.vOrders ; GO CREATE VIEW Sales.vOrders WITH SCHEMABINDING AS SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o WHERE od.SalesOrderID = o.SalesOrderID GROUP BY OrderDate, ProductID; GO
3. Create an index on the view. The Index on the view should be unique clustered index.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
Indexed views can provide a very significant improvement in performance.While the improvement in query performance can be substantial, the costs can be large enough to offset any benefits. Maintaining the index on a view has performance costs: it adds overhead to every insert, update or delete on a base table that participates in the view. This limits the applicability of indexed views to situations where data modifications are limited; preferably, where the data modification occurs in batches. Also, don't forget about the extra space consumed by the index.