Indexed Views InDepth

To create an index on a view in SQL Server 2005, the view definition must not contain:

• ANY, NOT ANY
• OPENROWSET, OPENQUERY, OPENDATASOURCE
• arithmetic on imprecise (float, real) values
• OPENXML
• COMPUTE, COMPUTE BY
• ORDER BY
• CONVERT producing an imprecise result
• OUTER join
• COUNT(*)
• reference to a base table with a disabled clustered index
• GROUP BY ALL
• reference to a table or function in a different database
• The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted
• computed columns.
• Derived table (subquery in FROM list) reference to another view
• DISTINCT
• ROWSET function
• EXISTS, NOT EXISTS
• self-join
• expressions on aggregate results (e.g. SUM(x)+SUM(x))
• STDEV, STDEVP, VAR, VARP, AVG
• full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
• Subquery
• imprecise constants (e.g. 2.34e5)
• SUM on nullable expressions
• inline or table-valued functions
• table hints (e.g. NOLOCK)
• MIN, MAX
• text, ntext, image, filestream, or XML columns
• non-deterministic expressions
• TOP
• non-unicode collations
• UNION

The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted computed columns.

GROUP BY Restrictions

If GROUP BY is present, the VIEW definition:

• Must contain COUNT_BIG(*).
• Must not contain HAVING, CUBE, ROLLUP, or GROUPING()

These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions

Index Requirements

The user executing the CREATE INDEX statement must be the view owner. If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause. The index must not be created with the IGNOREDUPKEY option enabled.

Index Views will not help in the following cases;

• OLTP systems that have many writes to the disk.
• Databases that have many updates.
• Queries that do not involve aggregations or joins.
• High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
• The overuse of indexed views can be like winding a watch with a wrench.
• Online index rebuilds are not supported for indexed views.