create precomputed result set

Creates precomputed result sets and the policies required to maintain them.

Syntax

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

Parameters

precomputed result set | materialized view – creates a materialized view or precomputed result set.
prs_name – name of the precomputed result set. A fully qualified prs_name cannot include the server or database name.
alternative_column_name – indicates the name for the precomputed result set column.
encrypt [with [database.[owner].]keyname]] [decrypt_default ] –
specifies an encrypted column.
- keyname – identifies a key created using create encryption key. The table owner must have select permission on keyname. If keyname is not supplied, the server looks for a default key created using create encryption key or alter encryption key.
- decrypt_default – specifies that this column returns a default value for users who do not have decrypt permissions.
See “Encrypting Data,” in the Encrypted Columns Users Guide for a list of supported datatypes.
constraint constraint_name – 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, the SAP ASE server generates a name in the format tabname_colname_tabindid, where tabindid is a string concatenation of the table ID and index ID.
unique (column_name,...) – constrains the values in the indicated column or columns so that no two rows have the same value.
{immediate | manual} refresh – 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, the SAP ASE 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.
populate | nonpopulate – 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. The SAP ASE server enables the precomputed result set the next time you issue the refresh command.
enable | disable – 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.
{enable | disable} use in optimization – 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.
lock {datarows | datapages | allpages} – indicates the level of locking the precomputed result set uses.

Examples

Example 1 – Creates the prs_1 precomputed result set:

create precomputed result set prs_1
as select col1, col2 from test_tab1e

Usage

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

You cannot include a like clause with create precomputed result sets or create materialized view that includes an immediate refresh parameter.

Standards

The create precomputed result set command is a Transact-SQL extension and is not covered by the SQL standard.

Permissions

You must have create table and create view privileges to create precomputed result sets.

Auditing

Creating precomputed result sets is not audited.