Neoview SQL Reference Manual (R2.4)

If the table is partitioned then the table is automatically partitioned across all the disk volumes
if the system has less than or equal to 256 disks. If the system has more than 256 disks, then the
table is partitioned across half the disks on the system.
Generating Values For an IDENTITY Column
You can use IDENTITY columns to automatically generate unique values. The values are unique
across all partitions of the table for the IDENTITY column. IDENTITY columns are declared in
the CREATE TABLE statement. IDENTITY columns can be used as surrogate keys. They can
also be used to uniquely identify records with the same key.
Difference Between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS
IDENTITY
GENERATED BY DEFAULT AS IDENTITY allows both user-supplied and system-generated
column values for the IDENTITY column. The GENERATED ALWAYS AS IDENTITY option
provides system-generated unique values. It does not allow user-supplied IDENTITY column
values.
Generating the System-Generated Value For an IDENTITY Column
Neoview SQL generates the next value for an IDENTITY column in these ways:
• An INSERT statement where DEFAULT is specified as a value for the IDENTITY column.
For example: INSERT INTO tbl1 (DEFAULT, 10); assuming the first column is defined as
an IDENTITY column.
• An INSERT statement specifying the columns to be inserted, but leaving out the IDENTITY
column. For example: INSERT INTO tbl1(b) values (10); assuming that tbl1 has an IDENTITY
column and the IDENTITY column has been omitted from the column list.
Generating Unique Values For an IDENTITY Column
The sequence generator feature provides a method for generating unique values for an IDENTITY
column. Neoview SQL guarantees to generate unique values if the IDENTITY column is defined
as GENERATED ALWAYS AS IDENTITY with the NO CYCLE option.
Available Values for an IDENTITY Column
The IDENTITY column can be defined as signed LARGEINT, unsigned INTEGER, and unsigned
SMALLINT. Each data type has a natural maximum value. The settings chosen for START WITH,
MINVALUE, MAXVALUE, and INCREMENT BY create a valid range of available numbers
before a maximum is reached. The settings MAXVALUE and INCREMENT BY can be altered
using ALTER TABLE ALTER COLUMN to change an artificially low range created during the
CREATE TABLE process. For more information, see the “ALTER TABLE Statement” (page 45).
Duplicate Values For an IDENTITY Column
Duplicates can be generated for IDENTITY columns if the IDENTITY column is defined as
GENERATED BY DEFAULT AS IDENTITY and you specify a duplicate value for the column.
Considerations
• If you specify GENERATED BY DEFAULT AS IDENTITY, values for the IDENTITY column
are generated by default. If you specify a value for the IDENTITY column, Neoview SQL
uses that value and does not generate a unique value for that row.
• IDENTITY columns can be the PRIMARY KEY or can be part of a compound clustering key.
You can alter the MAXVALUE and INCREMENT BY sequence generator options of an
IDENTITY column using the ALTER TABLE ALTER COLUMN statement.
CREATE TABLE Statement 85