Creating an Indexed View

Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements. The result set of a standard view is not stored permanently in the database. Each time a query references the view, Microsoft® SQL Server™ 2000 dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view. For more information, see View Resolution.

For a standard view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows, such as aggregating large amounts of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. For more information about the structure used to store clustered indexes, see Clustered Indexes.

Note  You can create indexed views only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded. For more information, see Using Indexes on Views.

Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views that are mapped over relatively static data, process many rows, and are referenced by many queries.

Requirements for the View

A view must meet these 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. SCHEMABINDING 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. 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 if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.
  
  
• The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
  • The select list cannot use 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, this select list is invalid:

    SELECT ColumnA, ColumnB, ColumnA
    

    These select lists are valid:

    SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColB
    
    SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB
    

  • A derived table.
    
    
  • Rowset functions.
    
    
  • UNION operator.
    
    
  • 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, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions.

    Complex aggregate function	Substitute simple aggregate functions
    AVG(X)

SUM(X), COUNT_BIG(X) STDEV(X)SUM(X), COUNT_BIG(X), SUM(X**2) STDEVP(X)SUM(X), COUNT_BIG(X), SUM(X**2) VAR(X)SUM(X), COUNT_BIG(X), SUM(X**2) VARP(X)SUM(X), COUNT_BIG(X), SUM(X**2)

For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).

A SUM function that references a nullable expression.

The full-text predicates CONTAINS or FREETEXT.

COMPUTE or COMPUTE BY clause.

If GROUP BY is not specified, the view select list cannot contain aggregate expressions.

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

A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table.

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.

The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements:

• The user executing the CREATE INDEX statement must be the view owner.
  
  
• These SET options must be set to ON when the CREATE INDEX statement is executed:
  • ANSI_NULLS
    
    
  • ANSI_PADDING
    
    
  • ANSI_WARNINGS
    
    
  • ARITHABORT
    
    
  • CONCAT_NULL_YIELDS_NULL
    
    
  • QUOTED_IDENTIFIERS
• The NUMERIC_ROUNDABORT option must be set to OFF.
  
  
• 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.

Considerations

After the clustered index is created, any connection attempting 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 proper option settings. For more information, see SET Options That Affect Results.

All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. 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 of 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:

• 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 participating 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. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

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

See Also

CREATE INDEX

SET ANSI_NULLS

SET ANSI_PADDING

SET ANSI_WARNINGS

SET ARITHABORT

SET CONCAT_NULL_YIELDS_NULL

SET NUMERIC_ROUNDABORT

SET QUOTED_IDENTIFIER