Creates precomputed result sets and the policies required to maintain them.
create {precomputed result set | materialized view}
[owner_name.]prs_name [(alternative_column_name
[[constraint constraint_name]
unique (column_name,...)]
[{immediate | manual} refresh]
[{populate | nopopulate}]
[enable | disable]
[{enable | disable} use in optimization]
[lock { datarows | datapages | allpages}]
[on segment_name]
[partition_clause]
as query_expression
creates a materialized view or precomputed result set.
name of the precomputed result set. A fully qualified prs_name cannot include the server or database name.
indicates the name for the precomputed result set column.
introduces the name of an integrity constraint, which must conform to the rules for identifiers and be unique in the database.
If you do not specify the name for a unique or primary-key constraint, Adaptive Server generates a name in the format tabname_colname_tabindid, where tabindid is a string concatenation of the table ID and index ID.
constrains the values in the indicated column or columns so that no two rows have the same value.
determines the refresh policy:
immediate – (default) updates the precomputed result set during the same transaction that updates the base tables.
manual – explicitly updates the precomputed result set. When you use the manual parameter, updates to the base tables are not reflected in the precomputed result set until you explicitly issue refresh. Because manual precomputed result sets are not maintained, Adaptive Server considers them to be stale, even after you issue the refresh parameter. Therefore, the query processor selects this data for query rewrite only if the query accepts stale data.
specifies whether to populate the precomputed result set after it is created, or create it with only the metadata information, and populate it later:
populate – (default) result set is populated as part of the create command.
nonpopulate – result set is not populated as part of the create command. If you specify nonpopulate, you cannot run the enable parameter on the precomputed result set. Adaptive Server enables the precomputed result set the next time you issue the refresh command.
specifies whether the precomputed result set is available for operations. This option overrides all other precomputed result set options.
enable – (default) is available for operations. Only precomputed result sets configured for enable are maintained according to their refresh policy.
disable – is not available for operations. Disabled precomputed result sets are not considered for maintenance or query rewrites. If a precomputed result set is configured for disable, it is not:
Used for the query rewrite during optimization, whether or not you specify use in optimization.
Populated, whether or not you specify with populate.
specifies whether to include a precomputed result set for query rewrite during optimization. use in optimization is enabled by default. Precomputed result sets are considered for query rewrite depending on how their refresh parameter is set:
immediate – is considered for all queries.
manual – is considered only if the query accepts with stale data.
indicates the level of locking the precomputed result set uses.
Creates the prs_1 precomputed result set:
create precomputed result set prs_1 as select col1, col2 from test_tab1e
You must set these set parameters before you create or alter precomputed result sets:
set ansinull on
set arithabort on
set arithignore off
set string_rtruncation on
The create precomputed result set command is a Transact-SQL extension and is not covered by the SQL standard.
You must have create table and create view privileges to create precomputed result sets.
Creating precomputed result sets is not audited.
