Precomputed result sets include restrictions.
- References to another precomputed result set. However, precomputed result sets
can reference a view if you create the precomputed result set with a manual
refresh policy.
- Expressions in the select list. However, you can include an
expression as part of the group by list.
- Encrypted columns, or result sets that encrypt their own column data.
- References to tables or functions in another database.
- References to virtual computed columns in a base table. However, precomputed
result sets can refer to materialized computed columns in a base table.
- compute, compute by, group by
all, or order by clauses.
- Nondeterministic functions (for example, getdate).
- XML.
- Subqueries.
- Outer and semijoins.
- Calls to user-defined functions.
- Derived tables.
- Reference to system, temporary, or fake tables.
- Union clauses.
- Any constraint other than unique key constraints.
- Columns that are defined with identity, null, or not null clauses.
- Defaults or rules.
- Cursors.
- Statistical aggregate functions.
- text, image, or unitext
columns.
In addition to the previous restrictions, precomputed result sets using the immediate
refresh policy cannot include:
- top, min, max, and
avg commands
- distinct clauses
- Self joins
- Functions
- References to proxy tables
- References to a view
- having clauses that reference the result of an aggregate
- sum functions that reference a nullable expression