SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-118
Considerations for CREATE TABLE

an increment

a cycle option
See CREATE TABLE Statement on page 2-95 for more information.
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-122.
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.
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