SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-91
Examples of CREATE TABLE
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. When you use the POS feature, you cannot choose
partitioning columns for automatically created partitions. The partitioning columns of
automated partitioned tables created through MXCS/JDBC sessions using POS are
the same as the clustering key columns of the table.
SQL/MX Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. SQL/MX
extensions to the CREATE TABLE statement are [NOT] DROPPABLE, ASCENDING,
DESCENDING, STORE BY, LOCATION, PARTITION, ATTRIBUTE, and LIKE clauses.
Examples of CREATE TABLE
•
This example creates a table stored by primary key. The clustering key is the
primary key.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
STORE BY PRIMARY KEY;
•
This example creates a table stored by the key column list. The clustering key is
ordernum, partnum, SYSKEY.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL)
STORE BY (ordernum, partnum);
•
This example creates a table stored by the SYSKEY. The clustering key is the
SYSKEY, type LARGEINT.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL)
;