Neoview SQL Reference Manual (R2.5)

For IDENTITY columns, the GENERATED BY DEFAULT AS IDENTITY option allows you to
supply the value or use the system-generated value. The GENERATED ALWAYS AS IDENTITY
option provides system-generated unique values only. If the values are user-provided, duplicates
could occur. In that case, the duplicate rows are automatically ignored and no errors occur.
Restrictions for CREATE SET TABLE
• CREATE SET TABLE is applicable only on base tables and is not supported for index tables.
• A row that contains default values for added columns cannot be treated as identical if the
inserting row contains the same default values in the corresponding columns.
Example for CREATE SET TABLE
In this example, the duplicate row is discarded.
>> CREATE SET TABLE t1(a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
PRIMARY KEY, b INT);
--- SQL operation complete.
>>insert into t1 values (1,2);
--- 1 row(s) inserted.
>>insert into t1 values (1,2);
--- 0 row(s) inserted.
Considerations for CREATE VOLATILE TABLE
• Volatile temporary tables are closely linked to the session. Their namespace is unique across
multiple concurrent sessions, and therefore allow multiple sessions to use the same volatile
temporary table names simultaneously without any conflicts.
• Volatile tables support creation of indexes.
• Volatile tables are partitioned by the system. The number of partitions is limited to 4 partitions
by default on the local segment from where the CREATE VOLATILE TABLE is issued. The
default value is 4 partitions regardless of the system configuration (16p, 32p, 64p, and so
on).
• Statistics are not automatically updated for volatile tables. If you need statistics, you must
explicitly run UPDATE STATISTICS.
• Volatile tables can be created and accessed using one-part name or two-part names. However,
you must use the same name (one part or two part) for any further DDL or DML statements
on the created volatile table. See “Examples of CREATE TABLE” (page 93).
• Neoview SQL allows users to explicitly specify primary key, STORE BY, and HASH
PARTITION BY clauses on columns that contain null values.
• Neoview SQL does not require that the first column in a volatile table contain not null values
and be the primary key. Instead, Neoview SQL attempts to partition the table, if possible,
using an appropriate suitable key column as the primary and partitioning key. For more
information, see “How Neoview SQL Selects Suitable Keys for Volatile Tables” (page 80).
Restrictions for CREATE VOLATILE TABLE
These items are not supported for volatile tables:
• ALTER statement
• User constraints
• Creating views, triggers, materialized views
• Creating non-volatile indexes on a volatile table or a volatile index on a non-volatile table
• CREATE TABLE LIKE operations
• Utility operations, such as MAINTAIN and PURGEDATA
CREATE TABLE Statement 79