Tuesday, June 14, 2011

Indexed View Limitations

There are several requirements that you must take into consideration when using Indexed views.
1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
a. TOP
b. Text, ntext or image columns
c. DISTINCT
d. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
e. SUM on a nullable expression
f. A derived table
g. Rowset function
h. Another view
i. UNION
j. Subqueries, outer joins, self joins
k. Full-text predicates like CONTAIN or FREETEXT
l. COMPUTE or COMPUTE BY
m. Cannot include order by in view definition

No comments:

Post a Comment