Creating Indexed Views

A view must meet the following requirements before you can create a clustered index on it:

• The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

• The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

• The view must not reference any other views, only base tables.

• All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

• The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

• User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

• Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

• All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.

  Note

  When you refer to datetime and smalldatetime string literals in indexed views in SQL Server 2008, we recommend that you explicitly convert the literal to the date type you want by using a deterministic date format style. For a list of the date format styles that are deterministic, see CAST and CONVERT (Transact-SQL). Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic, unless the compatibility level is set to 80 or earlier. This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting because the string 'listopad' means different months in different languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), SQL Server interprets the string '2000-12-01' based on the DATEFORMAT setting.

  Implicit conversion of non-Unicode character data between collations is also considered nondeterministic, unless the compatibility level is set to 80 or earlier.

  Creating indexes on views that contain these expressions is not allowed in 90 compatibility mode. However, existing views that contain these expressions from an upgraded database are maintainable. If you use indexed views that contain implicit string to date conversions, be certain that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications to avoid possible indexed view corruption.

• If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).

• The data access property of a user-defined function must be NO SQL, and external access property must be NO.

• Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

• CLR functions and methods of CLR user-defined types used in the view definition must have the properties set as shown in the following table.

  Property	Note
  DETERMINISTIC = TRUE	Must be declared explicitly as an attribute of the Microsoft .NET Framework method
  PRECISE = TRUE	Must be declared explicitly as an attribute of the .NET Framework method.
  DATA ACCESS = NO SQL	Determined by setting DataAccess attribute to DataAccessKind.None and SystemDataAccess attribute to SystemDataAccessKind.None.
  EXTERNAL ACCESS = NO	This property defaults to NO for CLR routines.

  For more information about how to set attributes of CLR routine methods, see Custom Attributes for CLR Routines.

  Warning

  We do not recommend setting the properties of CLR routine methods in contradiction to the functionality of the method. Doing this could lead to data corruption.

• The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:

  • The * or table_name**.*** syntax to specify columns. Column names must be explicitly stated.

  • A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, the following select list is not valid:

    SELECT ColumnA, ColumnB, ColumnA
    

    This select list is valid:

    SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB
    

  • An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.

  • A derived table.

  • A common table expression (CTE).

  • Rowset functions.

  • UNION, EXCEPT or INTERSECT operators.

  • Subqueries.

  • Outer or self joins.

  • TOP clause.

  • ORDER BY clause.

  • DISTINCT keyword.

  • COUNT (COUNT_BIG(*) is allowed.)

  • The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG(expression) is specified in queries referencing the indexed view, the optimizer can frequently calculate the needed result if the view select list contains SUM(expression) and COUNT_BIG(expression). For example, an indexed view SELECT list cannot contain the expression AVG(column1). If the view SELECT list contains the expressions SUM(column1) and COUNT_BIG(column1), SQL Server can calculate the average for a query that references the view and specifies AVG(column1).

  • A SUM function that references a nullable expression.

  • The OVER clause, which includes ranking or aggregate window functions.

  • A CLR user-defined aggregate function.

  • The full-text predicates CONTAINS or FREETEXT.

  • COMPUTE or COMPUTE BY clause.

  • The CROSS APPLY or OUTER APPLY operators.

  • The PIVOT or UNPIVOT operators

  • Table hints (applies to compatibility level of 90 or higher only).

  • Join hints.

  • Direct references to Xquery expressions. Indirect references, such as Xquery expressions inside a schema-bound user-defined function, are acceptable.

• If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.

Requirements for the CREATE INDEX Statement

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX (Transact-SQL).

The CREATE INDEX statement must meet the following requirements as well as the regular CREATE INDEX requirements:

• The user that executes the CREATE INDEX statement must be the view owner.

• The following SET options must be set to ON when the CREATE INDEX statement is executed:

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

• The NUMERIC_ROUNDABORT option must be set to OFF. This is the default setting.

• If the database is running in 80 compatibility mode or earlier, the ARITHABORT option must be set to ON.

• When you create a clustered or nonclustered index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

• The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.

• If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.

• An imprecise expression that forms the value of an index key column must reference a stored column in a base table underlying the view. This column may be a regular stored column or a persisted computed column. No other imprecise expressions can be part of the key column of an indexed view.

Considerations

The setting of the large_value_types_out_of_row option of columns in an indexed view is inherited from the setting of the corresponding column in the base table. This value is set by using sp_tableoption. The default setting for columns formed from expressions is 0. This means that large value types are stored in-row. For more information, see Using Large-Value Data Types.

After the clustered index is created, any connection that tries to modify the base data for the view must also have the same option settings required to create the index. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statement that will affect the result set of the view if the connection executing the statement does not have the correct option settings. For more information, see SET Options That Affect Results.

All indexes on a view are dropped when the view is dropped. All nonclustered indexes and auto-created statistics on the view are dropped when the clustered index is dropped. User-created statistics on the view are maintained. Nonclustered indexes can be individually dropped. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Although only the columns that make up the clustered index key are specified in the CREATE UNIQUE CLUSTERED INDEX statement, the complete result set of the view is stored in the database. As in a clustered index on a base table, the B-tree structure of the clustered index contains only the key columns, but the data rows contain all the columns in the view result set.

If you want to add indexes to views in an existing system, you must schema bind any view on which you want to place an index. You can perform the following operations:

• Drop the view and re-create it specifying WITH SCHEMABINDING.

• You can create a second view that has the same text as the existing view but a different name. The optimizer considers the indexes on the new view, even if it is not directly referenced in the FROM clause of queries.

  Note

  Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. Additionally, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

You must make sure that the new view meets all the requirements of an indexed view. This may require that you change the ownership of the view and all base tables it references so they are all owned by the same user.

Indexes on tables and views can be disabled. When a clustered index on a table is disabled, indexes on views associated with the table are also disabled. For more information, see Disabling Indexes.

Example

The following example creates a view and an index on that view. Two queries are included that use the indexed view.

USE AdventureWorks;
GO
--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;
GO
--Create 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
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

See Also

Reference

CREATE INDEX (Transact-SQL)

SET ANSI_NULLS (Transact-SQL)

SET ANSI_PADDING (Transact-SQL)

SET ANSI_WARNINGS (Transact-SQL)

SET ARITHABORT (Transact-SQL)

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

SET NUMERIC_ROUNDABORT (Transact-SQL)

SET QUOTED_IDENTIFIER (Transact-SQL)

Concepts

Designing Indexed Views

Troubleshooting DBCC Errors on Indexed Views