Restrictions on materialized views

The following restrictions apply when creating, initializing, refreshing, and view matching materialized views:

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.
Do not include columns defined as TIMESTAMP WITH TIME ZONE in the materialized view. The value of the time_zone_adjustment option varies between connections based on their location and the time of year, resulting in incorrect results and unexpected behavior.
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
- Transact-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
- divide_by_zero_error=On
- sort_collation=Internal
- 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
- timestamp_with_time_zone_format
- default_timestamp_increment
- uuid_has_hyphens
When a view is refreshed, the connection settings for all 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 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:

Note

You can use the sa_materialized_view_can_be_immediate system procedure to find out if a manual view is eligible to become an immediate view. See sa_materialized_view_can_be_immediate system procedure.

The view must be uninitialized. See Materialized view statuses and properties.
If the view does not contain outer joins, then the view must have a unique index on non nullable columns. If the view contains outer joins, the view must have a unique index on non nullable columns, or a unique index declared as WITH NULLS NOT DISTINCT on nullable columns. See Create 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:
- GROUPING SETS clauses
- CUBE clauses
- ROLLUP clauses
- DISTINCT clauses
- row limit clauses
- non-deterministic expressions
- self and recursive joins
- LATERAL, CROSS APPLY, or APPLY clauses
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.
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.
An aggregate function in the select list cannot be referenced in a complex expression. For example, SUM(expression) + 1 is not allowed in the select list.
If the select list contains the SUM(expression) aggregate function and expression is a nullable expression, then the select list must include a COUNT(expression) aggregate function.
If the view definition contains outer joins (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN) then the view definition must satisfy the following extra conditions:
1. If a table, T, is referenced in an ON condition of an OUTER JOIN as a preserved side, then T must have a primary key and the primary key columns must be present in the select list of the view. For example, the immediate materialized view V defined as SELECT T1.pk, R1.X FROM T1, T2 LEFT OUTER JOIN (R1 KEY JOIN R2) ON T1.Y = R.Y has the preserved table, T1, referenced in the ON clause and its primary key column, T1.pk, is in the select list of the immediate materialized view, V.
2. For each NULL-supplying side of an outer join, there must be at least one base table such that one of its non-nullable columns is present in the select list of the immediate materialized view. For example, for the immediate materialized view, V, defined as SELECT T1.pk, R1.X FROM T1, T2 LEFT OUTER JOIN (R1 KEY JOIN R2) ON T1.Y = R1.Y, the NULL-supplying side of the left outer join is the table expression (R1 KEY JOIN R2). The column R1.X is in the select list of the V and R1.X is a non nullable column of the table R1.
3. If the view is a grouped view and the previous condition does not hold, then for each NULL-supplying side of an outer join, there must be at least one base table, T, such that one of its non-nullable columns, T.C, is used in the aggregate function COUNT(T.C ) in the select list of the immediate materialized view. For example, for the immediate materialized view, V, is defined as SELECT T1.pk, COUNT(R1.X) FROM T1, T2 LEFT OUTER JOIN (R1 KEY JOIN R2) ON T1.Y = R1.Y GROUP BY T1.pk, the NULL-supplying side of the left outer join is the table expression (R1 KEY JOIN R2). The aggregate function COUNT( R1.X ) is in the select list of the V and R1.X is a non-nullable column of the table R1.
4. The following conditions must be satisfied by the predicates of the views with outer joins:
  - The ON clause predicates for LEFT, RIGHT, and FULL OUTER JOINs must refer to both preserved and NULL-supplying table expression. For example, T LEFT OUTER JOIN R ON R.X = 1 does not satisfy this condition as the predicate R.X=1 references only the NULL-supplying side R.
  - Any predicate must reject NULL-supplied rows produced by a nested outer join. In other words, if a predicate refers to a table expression which is NULL-supplied by a nested outer join, then it must reject all rows which have nulls generated by that outer join.
    
    For example, the view V1 SELECT T1.pk, R1.X FROM T1, T2 LEFT OUTER JOIN (R1 KEY JOIN R2) ON (T1.Y = R1.Y) WHERE R1.Z = 10 has the predicate R1.Z=10 referencing the table R1 which can be NULL-supplied by the T2 LEFT OUTER JOIN (R1 KEY JOIN R2), hence it must reject any NULL-supplied rows. This is true because the predicate evaluates to UNKNOWN when the column R1.Z is NULL.
    
    However, the view V2 SELECT T1.pk, R1.X FROM T1, T2 LEFT OUTER JOIN (R1 KEY JOIN R2) ON (T1.Y = R1.Y) WHERE R1.Z IS NULL does not have this property. The predicate R1.Z IS NULL references the NULL-supplying side R1 but it evaluates to TRUE when the table R1 is NULL-supplied (i.e., the R1.Z column is null). Note that the property of rejecting NULL-supplied rows is not as restrictive as a NULL-intolerant property. For example, the predicate R.X IS NOT DISTINCT FROM T.X and rowid(T) IS NOT NULL is not NULL-intolerant on the table T as it evaluates to TRUE when T.X is NULL. However, the predicate rejects all the rows which are NULL-supplied on the base table T.