SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-129
Considerations for CREATE TABLE
SG Table
When an IDENTITY column is defined, a sequence generator table (SG Table) is
created. The SG Table contains a column, CURRENT_VALUE. The CURRENT_VALUE
represents the next available value for the IDENTITY column. Each SG Table contains
only one row, with a primary key value of zero. The name space and object type for the
table has a value SG.
* Indicates primary key
Use the sg-location clause during CREATE TABLE to specify a location for the SG
Table. If you do not specify a location, the system default location is used. Since an SG
Table has base table characteristics, it includes an entry in the metadata OBJECTS,
ACCESS_PATHS, COLS, and PARTITIONS tables, etc.
The SG Table is updated upon a request to obtain the next value from the sequence
generator. The CURRENT_VALUE from the SG Table is selected and returned as the
next value. Then, the CURRENT_VALUE is updated with CURRENT_VALUE plus
INCREMENT BY value specified in SG Attributes.
A new independent transaction different from the user transaction is started and
committed for the SG Table select and update operations. A separate ESP process
houses the operator that starts and commits this new transaction. To reduce the impact
from this overhead, see Sequence Generator Cache
on page 2-133.
Considerations for an IDENTITY column
•
If you define the IDENTITY column as GENERATED BY DEFAULT AS IDENTITY,
values for the IDENTITY column are generated by default. If you specify a value
for the IDENTITY column, NonStop SQL/MX uses that value and does not
generate a unique value for that row.
•
An IDENTITY column can be the primary key or part of the primary key.
•
An IDENTITY column can be the partitioning key or part of the partitioning key.
•
An IDENTITY column can be the clustering key or part of the clustering key.
•
An IDENTITY column can be defined on a HASH or a RANGE partitioned table.
•
An IDENTITY column can be part of an INDEX.
Table 2-3. SG Table for IDENTITY COLUMN
Column Number Column Name Data Type Description
*1 ZERO_PKCOL LARGEINT Primary key with
value zero
2 CURRENT_VALUE LARGEINT Contains the current
value of the IDEN-
TITY column