The following restrictions apply when creating, initializing and refreshing materialized views, and 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; everything 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 in order for the view to be used in by the optimizer.
The following database option settings are remembered for each materialized view when it is created. The current option values for the connection must match the values remembered for a materialized view, in order for the view to be used in optimization:
When a view is refreshed, the connection settings for all of the above options are ignored. Instead, the values for these settings at the time of the view's creation are used.
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.
This is different behavior than non-materialized views for which the ORDER BY clause orders the results returned.
For information on materialized views and view matching by the optimizer, see Improving performance with materialized views.