SQL Server: Create an index View

A view is also called a virtual table, because the result set returned by the view is in a similar format as a table composed of columns and rows, the method for referencing a view in an SQL statement is the same as that for referencing a table. The result set of the standard view is not permanently stored in the database. When you query each referenced view, Microsoft SQL Server 2000 dynamically merges the logic required to generate a view result set into the logic required to generate a complete query result set from the base table data. The process of generating view results is called view concrete. For more information, see view resolution.

For standard views, it is costly to dynamically generate a result set for queries in each referenced view, especially for those that involve complicated processing of a large number of rows (such as aggregating a large amount of data or joining many rows) the view is more impressive. If this type of view is often referenced in queries, you can create a unique clustered index on the view to improve performance. This view is executed when a unique clustered index is created on the view, and the storage method of the result set in the database is the same as that of the table with clustered index.

DescriptionYou can create an index view only when Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Development edition is installed.

Another benefit of creating an index on a view is that the query optimizer starts to use a View index in the query, rather than naming the view directly in the from clause. In this way, you can retrieve data from the index view without re-encoding. The resulting efficiency also benefits existing queries. For more information, see use indexes on a view.

Creating a clustered index on a view can store data that exists when an index is created. The index view also automatically reflects the changes made to the base table data after the index is created, which is the same as the index created on the base table. When the data in the base table is changed, the data stored in the index view also reflects the data changes. The clustered index of the view must be unique, which improves the efficiency of SQL Server in searching for rows affected by any data changes in the index.

The maintenance of the index view may be more complex than that of the base table. An index should be created on a view only when the retrieval speed of the view results exceeds the overhead required for modification. Such a view usually includes a view mapped to relatively static data, processing multiple rows, and referenced by many queries.

View requirements

Before creating a clustered index on a view, the view must meet the following requirements:

• When executing the create view statement, the options ansi_nulls and quoted_identifier must be set to on. The objectproperty function reports this information for the view through the execisansinullson or execisquotedidenton attribute.

• To execute all the create table statements to create a table referenced by the view, the ansi_nulls option must be set to on.
• A view cannot reference any other view. It can only reference a base table.
• All base tables referenced by a view must be in the same database as the view, and the owner must be the same as the view.
• You must use the schemabinding option to create a view. Schemabinding binds the view to the schema of the base table.
• You must have used the schemabinding option to create user-defined functions referenced in the view.
• Table and user-defined functions must be referenced by two names. The names of part 1, part 3, and Part 4 are not allowed.
• All functions referenced by expressions in the view must be deterministic. The isdeterministic attribute of the objectproperty function reports whether the user-defined function is deterministic. For more information, see deterministic and non-deterministic functions.
• The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:
  • The * or table_name. * syntax cannot be used to specify columns in the selection list. The column name must be explicitly given.

  • You cannot specify a column name for a table that is used as a simple expression in multiple view columns. If all (or only one exception) references to a column are part of a complex expression or a function parameter, the column can be referenced multiple times. For example, the following selection list is invalid:

    Select Columna, columnb, Columna

    The following options are valid:

    Select Columna, AVG (Columna), Columna + Column B as addcolacolbselect sum (Columna), Columna % columnb as modulocolacolb

  • Derived table.
  • A collection function.
  • Union operator.
  • Subquery.
  • Outer Join or self join.
  • Top clause.
  • Order by clause.
  • Distinct keyword.
  • Count (*) (count_big (*) is allowed (*).)
    
  • AVG, Max, Min, STDev, stdevp, VAR, or varp aggregate function. If AVG, Max, Min, STDev, stdevp, VAR, or varp are specified in the query that references the index view, if the view selection list contains the following replacement functions, the optimizer often calculates the required results.
    

    complex Aggregate functions	replace 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, the index view selection list cannot contain the expression AVG (somecolumn ). If the view selection list contains expressions sum (somecolumn) and count_big (somecolumn), SQL Server can calculate the average number of queries that reference the view and specify AVG (somecolumn.

  • Reference the sum function of an expression that can be empty.
  • Full-text predicates 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 the count_big (*) expression, and the view definition cannot specify having, cube, or rollup.
• A column generated by an expression that can be set to float or evaluated using a float expression cannot be used as the index key of the index view or table.

Requirements for the create index statement

The first index created on the view must be a unique clustered index. After creating a unique clustered index, you can create other non-clustered indexes. The index naming Rules on The View are the same as those 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 who executes the create index statement must be the view owner.
• When executing the create index statement, the following set options must be set to on:
  • Ansi_nulls
  • Ansi_padding
  • Ansi_warnings
  • Arithabort
  • Concat_null_yields_null
  • Quoted_identifiers
• The numeric_roundabort option must be set to off.
• A view 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 column specified in the group by clause.

Notes

After a clustered index is created, the options of any connection that tries to modify the basic data of the view must be the same as those required to create the index. If the connection to the execution statement is not properly set, 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 the results.

If the view is removed, all indexes in the view are also removed. If clustered indexes are removed, all non-clustered indexes in the view are also removed. Non-clustered indexes can be removed separately. Excluding clustered indexes on The View, the stored result set will be deleted, and the optimizer will process the view as it would in the standard view.

Although the create unique clustered index statement only specifies the columns that constitute the clustered index key, the complete result set of the view is stored in the database. Like clustered indexes on the base table, the B-Tree Structure of clustered indexes only contains key columns, but data rows contain all columns in the view result set.

To add an index to a view in an existing system, you must plan to bind any view to be indexed. Yes:

• Remove the view and re-create it by specifying with schemabinding.
• Create another view that has the same text as the existing view but has different names. The optimizer will consider the index on the new view, even if it is not directly referenced in the from clause of the query.

DescriptionYou cannot remove tables or views from views created using the schemabinding clause, unless the view has been removed or changed and no longer has schema binding. In addition, if you execute the alter table statement on the table that participates in the view with schema binding, and these statements affect the view definition, these statements will fail.

Make sure that the new view meets all requirements of the index view. This may require you to change the ownership of the view and all its referenced base tables so that they are owned by the same user.

From: http://goaler.xicp.net/ShowLog.asp? Id = 526