Indexed View in SQL Server

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

Since the indexed view exists on disk there are two types of overhead added to the database:

• The disk space taken up by the view
• The cost of maintaining the view on disk as the base tables are modified.

Both of these costs can be substantial and limit the usefulness of indexed views to situations where there is a high ratio of data retrieval operations to data modifications. They are best for decision support and analytic applications where the frequency of data modification is low. Adding indexed views to an OLTP type application may slow it down more than it helps.

What are the requirements for Indexed views?

There are several requirements that you must take into consideration when using Indexed views.

1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
   
   1. TOP
   2. Text, ntext or image columns
   3. DISTINCT
   4. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
   5. SUM on a nullable expression
   6. A derived table
   7. Rowset function
   8. Another view
   9. UNION
   10. Subqueries, outer joins, self joins
   11. Full-text predicates like CONTAIN or FREETEXT
   12. COMPUTE or COMPUTE BY
   13. Cannot include order by in view definition

How do I create an Indexed View?

1. Make sure that session properties are properly set.
2. Create a deterministic view with new SCHEMABINDING syntax.
3. Create unique Clustered Index.
4. 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);
GO

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