Improve performance with SQL Server 2005 indexed views A

I. Indexed views

For years, MicrosoftSQL Server has supported the creation of virtual tables called views. Typically, the main functions of these views are:

Provides a security mechanism that restricts users to a dataset in one or more base tables.

Provides a mechanism that allows developers to customize how users can view data stored in a base table in a logical way.

The capabilities of the SQL Server 2000,sql server view are extended to achieve system performance benefits. You can create a unique clustered index and nonclustered index on the view to improve the data access performance of the most complex queries. In SQL Server 2000 and 2005, a view with a unique clustered index is an indexed view. The content discussed in this article applies to SQL Server 2005, many of which are also applicable to SQL Server 2000.

Viewed from the perspective of a database management system (DBMS), a view is a description of the data (a type of metadata). When a typical view is created, metadata is defined by encapsulating a SELECT statement that defines a result set as a virtual table. When a view is referenced in the FROM clause of another query, the metadata is retrieved from the system directory and the reference extension metadata for the view is substituted. After the view is expanded, the SQL Server query optimizer compiles an execution plan for executing the query. The query optimizer searches for a set of possible execution plans for a query, and selects the lowest cost plan that can be found based on an estimate of the actual time required to execute each of the query plans.

For non indexed views, the portion of the view necessary to parse the query is materialized at run time. Any calculation (for example, join or aggregation) completes 1 when the query for each referencing view executes. When a unique clustered index is created on a view, the result set of the view is materialized and saved in the physical storage of the database, thereby saving the overhead of performing this high cost operation.

In query execution, indexed views can be used in two ways. A query can refer directly to an indexed view, or more importantly, if the query optimizer determines that the view can replace some or all of the queries in the lowest-cost query plan, you can select it. In the second case, the indexed view is used instead of the underlying table and its general index. You do not have to refer to a view in a query so that the query optimizer uses the view when the query executes. This allows existing applications to benefit from the newly created indexed view without having to make changes.

Note Indexed views are a feature of SQL Server 2000 and version 2005. In the Developer and Enterprise versions of SQL Server 2000 and 2005, the query processor uses an indexed view to resolve a query that matches the view on the structure, even if the view is not referenced by name. In other versions, you must refer to the view by name and use the NOEXPAND prompt to query the contents of the indexed view using the visual reference.

Improving performance through indexed views

Using indexes to improve query performance is not a new concept; However, indexed views provide some performance gains that cannot be achieved with the help of standard indexes. Indexed views can improve query performance in the following ways:

Aggregations can be calculated in advance and stored in an index to minimize high cost calculations when the query is executed.

You can join each table in advance and save the resulting dataset.

You can save a combination of joins or aggregations.

The figure illustrates the performance improvements that are typically achieved when the query optimizer uses indexed views. The queries listed are different in complexity (such as the number of aggregations calculated, the number of tables used, or the number of predicates) and contain millions of rows of tables from the real production environment.

Using Nonclustered indexes on views

Second, nonclustered indexes on views can provide better query performance. Like nonclustered indexes on a table, nonclustered indexes on views can provide more options for the query optimizer to select during compilation. For example, if a query contains a column that is not covered by a clustered index, the optimizer can select one or more secondary indexes in the plan to avoid a time-consuming, full scan of the indexed view or base table.

Adding an index to a schema increases the cost of the database because the index requires continuous maintenance. It is prudent to weigh the appropriate balance between the index quantity and the maintenance cost.