SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-117
Considerations for CREATE TABLE
NonStop SQL/MX will place the primary partition on $DATA1, the second partition
on $VOL1, the third partition on $VOL2, and the fourth partition on $VOL3.
Creating a Table Without STORE BY Clause or Primary Key
NonStop SQL/MX bases table partitioning on clustering key columns, specified by the
STORE BY clause or, if there is no STORE BY clause, the primary key columns. If you
do not specify the STORE BY or PRIMARY KEY columns on a table, NonStop
SQL/MX cannot partition the table. If you attempt to use POS with such a table, you
will not receive an error. POS creates a nonpartitioned table in the same way that
NonStop SQL/MX creates a nonpartitioned table without the LOCATION clause as part
of the CREATE TABLE statement. The location of this table is not based on
POS_LOCATIONS or automatic disk location.
Partitioning Columns
Use the PARTITION BY clause to decouple the partitioning key from the clustering key.
Without the PARTITION BY clause, the partitioning columns of the table are same as
the clustering key columns. POS can be used to create partitions automatically for
tables with decoupled keys.
IDENTITY Column and Internal Sequence Generators
An IDENTITY column is a numeric column in a table for which the system generates
unique values using the internal sequence generator.
ANSI SQL Standard 2003 suggests two ways of generating unique numeric values:
1. Internal Sequence Generator: An internal sequence generator is implicitly created
when an IDENTITY column is defined and is associated only with that IDENTITY
column.
2. External Sequence Generator: An external sequence generator is explicitly created
using the CREATE SEQUENCE statement. The external sequence generator is a
schema level database object that the application uses to generate values for a
numeric column. The values generated by the external sequence generator are
unique for that sequence generator and can be used to create unique values
across a set of tables in a schema.
Sequence generators contain the following attributes called SG Attributes:
•
a data type
•
a minimum value
•
a maximum value
•
a start value
Note. NonStop SQL/MX 3.1 only provides IDENTITY column support using internal sequence
generators. All references to sequence generator in this manual are related to the internal
sequence generator associated with an IDENTITY column.