Indexed Views

To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to the underlying tables and retrieves the data at the time it is called.

An indexed view on the other hand, is a normal view that takes a copy of the underlying data it points to, and stores it locally. This way, the joins and aggregations that are processed at run-time in a normal view, are already materialized, so when queried, it’s as fast as querying a normal table. Therefore, another name for indexed view is “materialized view”. This is what it’s called in Oracle.

Before deciding to use indexed views in your design, make sure to thoroughly read the restrictions for creating them below.

Creating

To make a normal view an indexed view, you need to do two things. First you need to enable SCHEMABINDING for the view. Schemabinding essentially locks the underlying DDL schemas for the tables that the view references. This prevents any DDL changes from being made to the referenced tables. If you want to make a change to the tables, you need to drop the view first.

Let’s create this new view using the AdventureWorks Database:

[cc lang=”sql”]
CREATE VIEW Sales.OrderTotals
WITH SCHEMABINDING
AS

SELECT
SalesOrderID = soh.SalesOrderID,
OrderTotal = SUM(sod.UnitPrice),
OrderCount = COUNT_BIG(*)
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY
soh.SalesOrderID
[/cc]

Okay, we’re half way done. Now we need to create a unique clustered index on the view. As you know, a clustered index is essentially a table sorted by it’s indexed keys. So once we create this unique clustered index this materializes all the data. And yes, it does need to be unique:

[cc lang=”sql”]
CREATE UNIQUE CLUSTERED INDEX IDX_C_vw_Sales_OrderTotals_SalesOrderID
ON Sales.OrderTotals
(
SalesOrderID
)
[/cc]

There, now we have our indexed view completed. If we query the view and look at the execution plan, we should see that the newly created clustered index is referenced.

[cc lang=”sql”]
SELECT *
FROM Sales.OrderTotals
[/cc]

There are times however when the optimizer chooses NOT to use the indexed view. I have run into issues with this even as late as SQL 2005. The workaround for this is to use the query hint NOEXPAND. Also if you do not want to use the indexed view, you can use the other query option listed below:

[cc lang=”sql”]
— Use the indexed view
SELECT *
FROM Sales.OrderTotals WITH (NOEXPAND)

— Bypass the indexed view
SELECT *
FROM Sales.OrderTotals
OPTION (EXPAND VIEWS)
[/cc]

View Restrictions

There are also many restrictions in creating an indexed view. I recommend you review these now so you don’t learn the hard way.

The view definition must not contain:

• ANY, NOT ANY
• OPENROWSET, OPENQUERY, OPENDATASOURCE
• arithmetic on imprecise (float, real) values
• OPENXML
• COMPUTE, COMPUTE BY
• ORDER BY
• CONVERT producing an imprecise result
• OUTER join
• COUNT(*)
• reference to a base table with a disabled clustered index
• GROUP BY ALL
• reference to a table or function in a different database
• Derived table (subquery in FROM list)
• reference to another view
• DISTINCT
• ROWSET function
• EXISTS, NOT EXISTS
• self-join
• expressions on aggregate results (e.g. SUM(x)+SUM(x))
• STDEV, STDEVP, VAR, VARP, AVG
• full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
• Subquery
• imprecise constants (e.g. 2.34e5)
• SUM on nullable expressions
• inline or table-valued functions
• table hints (e.g. NOLOCK)
• MIN, MAX
• text, ntext, image, filestream, or XML columns
• non-deterministic expressions
• TOP
• non-unicode collations
• UNION
• contradictions SQL Server 2005 can detect that mean the view would be empty (e.g. where 0=1 and …)

Criticism in Designs

Sometimes in writing these articles, I feel like one of the old men (Statler & Waldorf) from the Muppet Show.. The reason is because I am often critical of the use of some features in SQL Server. This would be one of them. Now I do agree that Indexed views are cool and a necessary functionality for SQL Server, however I always try to steer clear of them. Why? In my opinion, they make maintenance difficult. The reason is, the schemabinding option disallows certain DDL modifications on referenced objects. Because of this, if you do want to make a changed to a referenced object, you need to drop the indexed view first. Well, if you need to do this, you also need a maintenance window. And hopefully the view and index are not too big, otherwise recreating could take you a while.

The other reason is the unpredictable need for the NOEXPAND hint. In the cases where I’ve wanted to create an indexed view, I wanted the new indexed view to replace a table that many stored procedures were referencing. So my plans were to create the new indexed view, rename the table to something like table_old, then rename the view to the original table name. Great.. that would work, however in reality every time I’ve done that, I also needed to add the NOEXPAND hint to each query so it would query the indexed view by default and not bypass it and use the underlying tables. For some reason the optimizer would choose not to reference the view. This foiled my plans because the point was to not touch the code in the stored procedures, but instead just rename the view to the table’s name.

So what is the workaround to not having to create an indexed view? Since the biggest use for indexed views I see is aggregations, I prefer to handle my own aggregations using a job and staging tables. Now, this cannot be done in real-time, so if you do need real-time you need to either do it in a trigger (ugh) or through the indexed view. But luckily in all my dealings, the incremental staging process has worked out fine.

2 comments

Will Spurgeon (@willtonic) 24 Feb 2012 at 10:23 am

Thanks for sharing your criticism of indexed views – this kind of contextual advice is exactly what I need when trying to make a decision about whether to embrace a feature. Good stuff.

Reply to this comment