Indexed Views

It is possible to materialize a view—to create a table in the database that will contain all the data that is referenced by a view. This technique can significantly improve the performance of a Select statement when SQL Server has to join many tables, and can return or aggregate a large number of records.

When you create a unique clustered index on a view, SQL Server materializes the view. Records are saved in the database in the same manner that clustered indexes on regular tables are stored:

      Create View vLaptopInventory      WITH SCHEMABINDING      as      select i.Inventoryid, i.EqId, i.StatusId, e.Make, e.Model      from dbo.Inventory I      inner join dbo.Equipment e      on i.EqId = e.EqId      where EqTypeId = 1      GO      CREATE UNIQUE CLUSTERED INDEX idxvLaptdpInventory      ON vLaptopInventory (InventoryId) 

Although the index references only a subset of columns, the index (indexed view) contains all columns in the leaf-level nodes (as does every clustered index).

Indexed View Limitations

There are many limitations with which a view must comply to be converted to an indexed view:

• The view must be created using the With Schemabinding option.

• The view must reference only tables—not other views, derived tables, rowset functions, or subqueries.

• All base tables must have the same owner as the view.

• The view cannot join tables from more than one database.

• The view cannot contain an outer-or self-join.

• The view cannot have a Union, Top, or Order By clause, or a Distinct keyword.

• Some aggregate functions are not allowed: Count(*) [use Count_Big(*) instead], Avg(), Max(), Min(), Stdev(), StdevpO, Var(), or Varp(). But all of these aggregate functions can be re-engineered using valid functions [such as Sum() and Count_Big(*)].

• If a query contains a Group By clause, it must contain Count_Big(*) in the Select list.

• The view and all base tables must be created with Set Ansi_Nulls On.

• All tables and user-defined functions in the view must be referenced using two-part names (owner.dbobject).

  All columns must be explicitly specified—Select * is not allowed.

• The view cannot contain text, ntext, or image columns.

• Having, Rollup, Cube, Compute, and Compute By clauses are not allowed.

• The same table column must not be converted to more than a single view column.

• You can only create indexed views in the Enterprise and Developer editions of SQL Server.

• The Create Index statement and all subsequent Insert, Update, and Delete statements must be executed with the following option settings (explicitly or implicitly):

        Set ANSI_NULLS ON      Set ANSI_PADDING ON      Set ANSI_WARNINGS ON      Set ARITHABORT ON      Set CONCAT_NULL_YIELDS_NULL ON      Set QUOTED_IDENTIFIERS ON      Set NUMERIC ROUNDABORT OFF 

Indexed Views in Execution Plans

Optimizer treats indexed views as tables. SQL Server simply joins them with other tables. There is one exception—Optimizer can use an indexed view even when the view is not explicitly referenced in the query (when the query is referencing only some of the base tables). SQL Server compares the cost of the execution plan with base tables and the execution plan with the indexed view and chooses the cheapest one.

You can force SQL Server to ignore the indexed view using the Expand View hint. Conversely, you can also force SQL Server to use the indexed view using the Noexpand hint.

Nonclustered Indexes on Views

Once a clustered index is added to a view, you can add more nonclustered indexes:

      CREATE INDEX idxvLaptopInventory_MakeModel      ON dbo.vLaptopInventory (Make, Model) 

Performance Implications

Indexed views typically improve the performance of data warehouse systems and other systems that predominantly have queries that read data. On the other hand, indexed views can reduce the performance of OLTP systems. Updates to an indexed view become part of transactions that modify the base tables. This fact may increase the cost of OLTP transactions and offset the savings achieved on read operations.