SQL Server: Creating an indexed view

server| Create | view | index
A view is also called a virtual table, because the result set returned by the view has a generic format similar to a table composed of columns and rows, and the view is referenced in SQL statements in the same way as a reference table. The result set of the standard view is not stored permanently in the database. Each time a query references a view, Microsoft®sql server™2000 dynamically merges the logic required to generate the view result set into the logic necessary to generate a full query result set from the base table data. The process of generating view results is called view materialization. For more information, see View Resolution.

For a standard view, it is expensive to dynamically generate a result set for each query that references a view, especially for views that involve complex processing of a large number of rows, such as aggregating large amounts of data or joining many rows. If you frequently refer to such views in a query, you can improve performance by creating a unique clustered index on the view. The view is executed when a unique clustered index is created on the view, and the result set is stored in the database in the same way as the table with the clustered index.

Note You can create an indexed view only if you have Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Development Edition installed.



Another benefit of creating an index on a view is that the query optimizer starts using the view index in the query instead of naming the view directly in the FROM clause. As a result, data can be retrieved from an indexed view without recoding, and the resulting efficiencies can also benefit existing queries. For more information, see Using Indexes on views.

Create a clustered index on a view to store the data that exists when the index is created. The indexed view also automatically reflects changes made to the base table data since the index was created, as is the index created on the base table. When you make changes to the data in the base table, the data stored in the indexed view also reflects the data changes. The view's clustered index must be unique, which improves the efficiency of SQL Server in finding rows affected by any data changes in the index.

Maintaining an indexed view can be more complex than an index on a base table. You should create an index on a view only if the result of the view is more efficient than the cost of modifying it. Such views typically include views that are mapped to relatively static data, handle multiple rows, and are referenced by many queries.
Requirements for views
Before you create a clustered index on a view, the view must meet the following requirements: When you execute the CREATE VIEW statement, the ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON. The OBJECTPROPERTY function reports this information to the view through the Execisansinullson or Execisquotedidenton properties.

To execute all CREATE TABLE statements to create a table that is referenced by the view, the ANSI_NULLS option must be set to ON.

Views cannot refer to any other view, only base tables.

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

You must use the SCHEMABINDING option to create a view. Schemabinding the schema that binds the view to the underlying base table.

You must have created a user-defined function that is referenced in the view using the Schemabinding option.

Tables and user-defined functions must be referenced by a 2-part name. 1-part, 3-part and 4-part names are not allowed.

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

The SELECT statement in the view cannot contain the following Transact-SQL syntax elements: The select list cannot specify columns using the * or table_name.* syntax. The column name must be explicitly given.

You cannot specify the column name of a table that is used as a simple expression in more than one view column. If all (or only one exception) references to a column are part of a complex expression or a parameter of a function, you can refer to the column multiple times. For example, the following list of choices is illegal:
SELECT ColumnA, COLUMNB, ColumnA

The following list of choices is legal:

SELECT ColumnA, AVG (ColumnA), ColumnA + Column B as Addcolacolbselect SUM (ColumnA), ColumnA% COLUMNB as Modulocolacolb
Derived tables.

Rowset functions.

The UNION operator.

Subquery.

Outer joins or self joins.

The TOP clause.

The ORDER BY clause.

DISTINCT keyword.

COUNT (*) (Allows COUNT_BIG (*). ）
AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If you specify AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP in a query that references an indexed view, the optimizer often calculates the desired results if the view selection list contains the following substitution functions. Complex aggregate functions instead of 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 selection list cannot contain an expression AVG (Somecolumn). If the view selection list contains expression SUM (Somecolumn) and COUNT_BIG (somecolumn), SQL Server can calculate averages for queries that reference the view and specify AVG (Somecolumn).
The SUM function that references a nullable expression. Full-text predicate CONTAINS or FREETEXT. COMPUTE or COMPUTE by clause. If GROUP by is not specified, the View selection list cannot contain an aggregate expression. If GROUP by is specified, the View selection list must contain an COUNT_BIG (*) expression, and the view definition cannot specify a having, CUBE, or ROLLUP. A column that is generated by an expression that can evaluate either a float value or a float expression cannot be a key to an indexed view or a table's index. Requirements for CREATE INDEX statements
The first index created on a view must be a unique clustered index. After creating a unique clustered index, you can create additional nonclustered indexes. The index naming rules on the view are the same as the index naming rules on the table. The only difference is that the table name is replaced by the view name. For more information, see CREATE INDEX.

In addition to the general create index requirements, the CREATE INDEX statement must also meet the following requirements: The user executing the CREATE INDEX statement must be the owner of the view. The following set options must be set to On:ansi_nullsansi_paddingansi_warningsarithabortconcat_null_yields_nullquoted_ when the CREATE INDEX statement is executed IDENTIFIERS must have the option Numeric_roundabort option set to OFF. Views cannot contain 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 only reference the columns specified in the GROUP by clause. Attention matters
After a clustered index is created, the option settings for any connection that attempts to modify the base data for the view must be the same as the option settings required to create the index. If the connection to the execution statement does not have the appropriate option settings, SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statements that affect the view result set. For more information, see SET options that affect results.

If you drop the view, all indexes on the view will also be dropped. If you drop the clustered index, all nonclustered indexes on the view are also dropped. Nonclustered indexes can be dropped separately. Dropping a clustered index on a view deletes the stored result set, and the optimizer processes the view again as it does with standard views.

Although the CREATE UNIQUE CLUSTERED index statement specifies only the columns that make up the clustered index key, the full result set of the view is stored in the database. As with 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 an index to a view in an existing system, you must plan to bind any view that you want to put in the index. You can: Drop the view and re-create it by specifying the with schemabinding. Create another view with the same text as the existing view, but with a different name. The optimizer takes into account the index on the new view, even if it is not referenced directly in the FROM clause of the query.

Note You cannot drop a table or view that participates in a view created with the SCHEMABINDING clause unless the view has been dropped or changed without schema binding. In addition, if you execute ALTER table statements on a table that participates in a view with schema binding, and those statements affect the view definition, those statements will fail.


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

Turn from: http://goaler.xicp.net/ShowLog.asp?ID=526