Indexed Views in SQL Server

Before having a discussion on “Indexed views in SQL Server”, let’s talk about simple view first.

A view is a virtual table which contains a SELECT statement to be executed when this view is called. It does not store any physical data and processes the data at run time. This can be used as a table and security principals can be applied on it. So instead of a table, a view can be exposed to the user. Using views, we can also take advantage of exposing only some columns of the given table instead of all columns, which increases the security. But in case of a view, each time it’s called, query inside the view gets executed.

Now come to the point of discussion of this post, an indexed view is a type of view which can materialize it’s data and instead of getting data by querying the underlying tables each time, it serves the physically stored data. So instead of querying base tables of underlying query to extract the data, it serves the materialized result set and increases the performance by removing the overhead of data processing. It removes the overhead of dynamically building the result set from complex query and a lot of aggregates. Once the view is materialized, multiple non-clustered indexes can be created on it which can serve more queries. It also increases the maintenance overhead. Let’s cover this maintenance topic in more detail at the end of the article.

How to create indexed views

To create an indexed view, just create an unique clustered index on the view. As and when you will create an unique clustered index on the view, it’s data gets materialized in the database.

Here I am taking “AdventureWorks2012” as a database to demonstrate my example and below is the definition of the view.

CREATE VIEW dbo.viewTestIndexedView
WITH SCHEMABINDING
AS
SELECT
PP.ProductID, PP.Name, PP.Color, SUM(SSO.UnitPrice) AS UnitPriceTotal, SUM(SSO.LineTotal) AS LineTotal,
COUNT_BIG(*) AS CountTotal
FROM Production.Product PP
INNER JOIN Sales.SalesOrderDetail SSO
ON PP.ProductID = SSO.ProductID
GROUP BY PP.ProductID, PP.Name, PP.Color
GO

Now, as per the requirement we have to make it materialized and to do this, we have to create a unique clustered index on it.

Before running Create Index command on view following SET options should be as;

• ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING : ON
• OFF: NUMERIC_ROUNDABORT : OFF

CREATE UNIQUE CLUSTERED INDEX cidx_viewTestIndexedView ON viewTestIndexedView(ProductID)

Have a look on the execution plan which ensures that the data is materialized at this stage;

Limitations of Indexed views creation : Important notes;

1. No outer joins allowed inside view definition:

Only inner joins are allowed inside view definition. In case you have Left, Right or Full join, index creation gets failed like this;

Msg 10113, Level 16, State 1, Line 1

Cannot create index on view “AdventureWorks2012.dbo.viewTestIndexedView” because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

2. Apply (Outer Apply or Cross Apply) not allowed:

In case you have an Outer apply or cross apply inside the view definition, you will get error message like below during index creation;

Msg 10142, Level 16, State 1, Line 1
Cannot create index on view “AdventureWorks2012.dbo.viewTestIndexedView” because it contains an APPLY. Consider not indexing the view, or removing APPLY.

3. View can refer only tables and not any other view inside view definition.

4. Tables referred by view must be in same database.

5. View must be entitled with SCHEMABINDING option.

6. User defined function can only be allowed inside view definition, if UDF is created with SCHEMABINDING option too.

7. Table and functions must be referenced with two-part naming convention – [SchemaName].[TableName] or [SchemaName].[FunctionName]

8. View definition must be deterministic – Can return only one possible result set for the given query.

9. List of SET options :

• ANSI_NULLS, QUOTED_IDENTIFIER – ON
• NUMERIC_ROUNDABORT – OFF
• ANSI_NULLS option should also ON for CREATE TABLE statements referenced by view

10. CLR functions can be used in select list of view but can not participate in clustered index definition also it can not be used in WHERE and JOIN clause.

11. SELECT * is not allowed in SELECT list.

Msg 10114, Level 16, State 1, Line 1
Cannot create index on view “AdventureWorks2012.dbo.viewTestIndexedView” because it uses the PIVOT operator. Consider not indexing this view.

13. Must use COUNT_BIG() with GROUP BY clause;

Msg 10138, Level 16, State 1, Line 1
Cannot create index on view ‘AdventureWorks2012.dbo.viewTestIndexedView’ because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

There are many more to consider during indexed view creation.

Performance and execution plan difference between simple view and indexed views:

Execution plan and statistics details with simple view:-

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM [HumanResources].[vEmployee] WHERE BusinessEntityID = 1

Table ‘CountryRegion’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘StateProvince’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Address’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘BusinessEntityAddress’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Employee’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Person’. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 0 ms,  elapsed time = 85 ms.

Execution plan with simple view;

Execution plan and statistics details with indexed view:-

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM dbo.viewTestIndexedView

Table ‘viewTestIndexedView’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 0 ms,  elapsed time = 49 ms

Execution plan with indexed view;

So, from above figures, you can see that in case of indexed view, optimizer is using the materialized data from created index. Now we can create multiple non-clustered indexes on this view also as like a table. If you have a look on statistics comparison of simple and indexed view, you can see a big difference.

Pros and Cons

Pros

Complex logic like joins, aggregates etc can be precomputed and materialized to save the effort of data processing for highly complex views which impacts large data sets. It reduces the processing of query inside the view by serving the data from stored result set.
One more important thing about the indexed view is that it also benefits the queries which are not directly based on the view but may be interested in the materialized data. These types of queries can be served from materialized data without any change in the query. Optimizer can take a decision to use the clustered index of view in such type of cases. But query optimizer takes such decisions only for non-trivial cost queries. Reporting systems can get benefit from indexed view a lot but it totally depends on your environment.

Cons

If the tables inside the view are highly up-datable, it will increase an extra overhead of “view update”. And in case view has  non-clustered indexes too, all non-clustered indexes also needs to be updated. In case of OLTP systems, we should always compare the cost of maintenance of indexed view with it’s benefit. It has very strict syntax and a lot of limitations too. Before going with indexed view you should ask yourself that “How much you will get from this indexed view in your environment in comparison of maintenance overhead”? Also don’t forget that it has a lot of limitations too.

For an example of maintenance overhead, i am trying to run an UPDATE statement on table “Production.Product” as below and sharing the execution plan below;

UPDATE Production.Product SET NAME = 'Adjustable Race Test' WHERE ProductID = 1

Now have a look on the below execution plan. The execution plan is too large to accommodate completely, just have a look on the circled area, which shows that during UPDATE, clustered index on the view is also being updated. Below is the screen shot;

Here we have only one clustered index with this view. As discussed above we can also have multiple non-clustered indexes on this view after creating the unique clustered index. And in this case it will also add the overhead cost of maintenance of all these non-clustered indexes too.

Conclusion

Many people like Column Store indexes instead of indexed views to materialize pre-aggregating data. The net benefit of having an indexed view is the difference of it’s maintenance cost vs effort it saves for you. Once again i would like to remind you that it has many limitations also and may not fit as a solution in all conditions. So before using it in your environment in OLTP systems, always keep in mind about the overheads of indexed views and limitations too.

I have tried to put most of the details related to indexed view here, but it’s not all. So as usual here i am requesting you to put your comments and suggestions in comments area at bottom of the page.

Do share if you like this post and don’t forget to rate it.