Indexed Views Basics in SQL Server

Restrictions on Indexing Views

There are restrictions on which base tables may be included in the view, which views are eligible for indexing, on the columns that can be in the view, and on which columns in those the view can be part of the index. It is also important to manage the database options that are in effect any time the view or its base tables are referenced by the database.

Indexed views require that a consistent set of session options exists during three time periods:

• When the indexed view is created
• When any INSERT, UPDATE, or DELETE operation is performed on a base table of the view.
• When the indexed view is used by the optimizer to produce a query plan

Since you never totally know when the indexed view might be affected, the options should be set all the time. The seven options are listed in Table 1 with the values that must be set and the default value for the option.

Table 1

Database access methods such as OLE DB, ODBC, and DB-Library set these options, but not to the exact values needed by indexed views. They should probably be set at the server level with sp_configure and careful attention should be paid to their values by setting them when any database connection is created.

The next sections review restrictions on the tables, views, and columns that you might try to include in a indexed view. Each section has a discussion of what the restrictions are, how to detect them, and live within them.

Restrictions on Base Tables

For starters, base tables of the view must be in the same database as the view. In addition, if there are computed fields in the base table that are reference by the view, a special condition applies: the value of ANSI_NULLS and QUOTED_IDENTIFIER must have the correct option when the base table is created. The reason they must be set when the base table is created, as opposed to at runtime, is that they are both parse time options. That is, the table retains the value that was set when the table was created. If the wrong options are in effect when the base tables referenced by an indexed view are created, the view cannot be created with SCHEMABINDING, which is a requirement for the view. Ordinary columns that are not computed are not affected by this requirement.

Restrictions on the View to be Indexed

When the views to be indexed is created it:

• Must be created the WITH SCHEMABINDING view option
• May only refer to base tables in the same database.
• If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
• May not have an OUTER JOIN clause.
• May not have a UNION.
• May not have DISTINCT or TOP clauses
• May not have full-text predicates such as CONATINSTABLE
• May not have a ROWSET function such as OPENROWSET
• May not use derived tables or subqueries.
• Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON

The SCHEMABINDING requirement adds additional requirements, such as the use of a two-part name for all base tables.

The Northwind.dbo.[Order Details Extended] view was shown above. It is not schema bound. This script creates a revised view that is schema bound:

CREATE VIEW OrderDetailsXSB   WITH SCHEMABINDING 
AS
SELECT OD.OrderID, OD.ProductID, P.ProductName , OD.UnitPrice
     , OD.Quantity, OD.Discount
FROM dbo.Products P
    INNER JOIN dbo.[Order Details] OD
         ON P.ProductID = OD.ProductID

There are three important changes made to create the new view:

• The WITH SCHEMABINDING option was added to the header.
• Two-part names are used for both base tables.
• ExtendedPrice was removed from the SELECT list. It is an expression and to be indexed, the view may not have any expressions.

The last change is pretty major but since the columns used to compute the ExtendedPrice column are all in the view, any statement selecting from the view could compute the extended price in an expression.

What is accomplished by this view is that the [Order Details] table and the Product table are pre-joined and the ProductName field is stored in the view with information from the [Order Details] table. Once the view is indexed and a statement uses the view, the join is not necessary; SQL Server can go directly to the rows stored in the view's clustered index.

The OBJECTPROPERTY built-in function has a property, IsIndexable, which can be used to find out if a view satisfies all the requirements for indexing. This query shows us the index eligibility of both the [Order Details] base table and the two views built on it:

-- Check index eligibility for 'Order Details%' tables and views
SELECT TABLE_TYPE, TABLE_NAME
     , OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsIndexable')
                          AS IsIndexable
   FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME LIKE ('Order Details%')
         OR TABLE_NAME = 'OrderDetailsXSB'
GO
(Results)
TABLE_TYPE TABLE_NAME                  IsIndexable 
---------- --------------------------- ----------- 
BASE TABLE Order Details                         1 
VIEW       Order Details Extended                0 
VIEW       OrderDetailsXSB                       1

From this query, we know that OrderDetailsXSB satisfies the conditions for indexability. That does not mean that every column in the view can be indexed. There are additional restrictions on the columns that may effect [Order Details SB]

Unfortunately, the IsIndexable property is imperfect. A view will still have a true value for IsIndexable even if it has expressions in columns. The problem only shows up when you attempt to create the index.

The limitation on expressions is hardly the only limitation on the columns in a view. The next section looks at restrictions on columns and shows you how to locate the columns that can be part of an index.

Previous Page (1)  : Next Page (3)  :  Last Page (4)