Improve performance with SQL Server 2005 indexed view Two

View Restrictions

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

Any, not any OPENROWSET, OPENQUERY, OpenDataSource

Arithmetic OPENXML on imprecise (floating, real) values

COMPUTE, COMPUTE by

CONVERT generates an inexact result OUTER join

COUNT (*) refers to a base table with a disabled clustered index

GROUP by all references tables or functions in different databases

A derived table (a subquery in the From list) references another view

DISTINCT ROWSET function

EXISTS, not EXISTS self-join

Aggregate results (for example: SUM (x) +sum (x)) expression STDEV, STDEVP, VAR, VARP, AVG

Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, freetexttable) subqueries

An inexact constant (for example: 2.34e5) A SUM on an empty expression

Inline or table-valued function table hints (e.g., NOLOCK)

MIN, MAX text, ntext, image, FileStream, or XML column

Non-deterministic expression top

Non-Unicode sort UNION

The conflicting scenarios that SQL Server 2005 can detect indicate that the view will be empty (for example, when 0=1 and ... ）

Note Indexed views may contain floating and solid columns, but they cannot be included in a clustered index key if such columns are not persisted.

GROUP by limit

If there is a GROUP By,view defined as:

Must contain COUNT_BIG (*).

Must not contain having, CUBE, ROLLUP, or GROUPING ().

These restrictions apply only to indexed view definitions. Even if the GROUP by limit is not met, the query can also use an indexed view in its execution plan.

Requirements for indexing

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 only refer to the column specified by the GROUP BY clause.

You must not create an index with the IGNORE_DUP_KEY option enabled.