Restrictions on materialized views
The following restrictions apply when creating, initializing, and refreshing materialized views, as well as during view matching,
as noted below:
- When creating a materialized view, the definition for the materialized view must define column names explicitly; you cannot
include a
SELECT * construct as part of the column definition.
- When creating a materialized view, the definition for the materialized view cannot contain:
- references to other views, materialized or not
- references to remote or temporary tables
- variables such as CURRENT USER; all expressions must be deterministic
- calls to stored procedures, user-defined functions, or external functions
- T-SQL outer joins
- FOR XML clauses
- The following database options must have the specified settings when a materialized view is created; otherwise, an error is
returned. These database option values are also required for the view to be used by the optimizer:
- ansinull=On
- conversion_error=On
- sort_collation=Null
- string_rtruncation=On
- The following database option settings are stored for each materialized view when it is created. The current option values
for the connection must match the values stored for a materialized view in order for the view to be used in optimization:
- date_format
- date_order
- default_timestamp_increment
- first_day_of_week
- nearest_century
- precision
- scale
- time_format
- timestamp_format
- When a view is refreshed, the connection settings for all of the options listed in the bullets above are ignored. Instead,
the database option settings (which must match the stored settings for the view) are used.
Specifying an ORDER BY clause in a materialized view definition
Materialized views are similar to base tables in that the rows are not stored in any particular order; the database server
orders the rows in the most efficient manner when computing the data. Therefore, specifying an ORDER BY clause in a materialized
view definition has no guaranteed impact on the ordering of rows when the view is materialized. Also, the ORDER BY clause
in the view's definition is ignored by the optimizer when performing view matching.
For information about materialized views and view matching by the optimizer, see Improving performance with materialized views.
Additional restrictions for immediate views
The following restrictions are checked when changing a manual view to an immediate view. An error is returned if the view
violates any of the restrictions:
- The view must be uninitialized. See Materialized view statuses and properties.
- The view must have a unique index on non-nullable columns. If it does not, you must add one. See Creating indexes.
- If the view definition is a grouped query, the unique index columns must correspond to select list items that are not aggregate
functions.
- The view definition cannot contain:
- a SUM function over a nullable expression.
- GROUPING SETS clauses
- CUBE clauses
- ROLLUP clauses
- LEFT OUTER JOIN clauses
- RIGHT OUTER JOIN clauses
- FULL OUTER JOIN clauses
- DISTINCT clauses
- row limit clauses
- non-deterministic expressions
- self and recursive joins
- The view definition must be a single select-project-join or grouped-select-project-join query block, and the grouped-select-project-join
query block cannot contain a HAVING clause. For example,
SUM(expression) + 1 is not allowed in the select list.
The grouped-select-project-join query block must contain COUNT(*) in the select list, and is allowed only the SUM and COUNT
aggregate functions.
For a description of these structures, see Materialized view evaluation step.
- An aggregate function in the select list cannot be referenced in a complex expression.
