SQL/MX 3.x Reference Manual (H06.22+, J06.11+)
SQL/MX Statements
HP NonStop SQL/MX Reference Manual—640322-001
2-94
Considerations for CREATE TABLE
For small tables, you need not be concerned about the details of this mechanism. For
large tables, however, the indexes used to enforce constraints can require significant
amounts of disk space. You might prefer to create and partition constraint-supporting
indexes directly so that you can control the use of disk space or so that you can specify
indexes that provide more effective access paths for your application than those
created by default to support constraints.
To create constraint-supporting indexes directly, use CREATE TABLE to create the
table without index-implemented constraints, use CREATE INDEX to create
appropriate indexes, and then use ALTER TABLE ... ADD CONSTRAINT to add
constraints to the table.
Limits for Tables
The maximum size of a row depends on the block size as described in Table 2-2.
If the table has a SYSKEY, the SYSKEY column requires 8 bytes. The number of bytes
required to store a column depends on the data type of that column. If the column is
nullable, NonStop SQL/MX uses an additional 2 bytes for the NULL indicator. Each
variable-length character column uses an additional 8 bytes for the column length.
There can be a maximum of 2100 columns in a row.
Tables and Triggers
The primary key length for a table with triggers cannot exceed 2032 bytes. A table that
does not have triggers can have a primary key of 2048 bytes. For information about
this limit, see Triggers and Primary Keys on page 2-107.
Creating Partitions Automatically
NonStop SQL/MX uses Partition Overlay Specification (POS) so that MXCI, MXCS,
JDBC T4, and JDBC T2 users can automatically create hash-partitioned tables with the
CREATE TABLE statement. NonStop SQL/MX does not support automatic creation of
range-partitioned tables.
Applications can control whether POS is enabled, the number of partitions, and the
physical location of the partitions.
The following CONTROL QUERY DEFAULT attributes determine the physical location
and the number of partitions:
•
POS_LOCATIONS
•
POS_NUM_OF_PARTNS
Table 2-2. Maximum Row Sizes Available
DP2 block size Max row size available to users Max # of Columns
4096 4036 2100
32768 32708 2100