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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-58
Index Keys
Partitioning character columns must derive from the ISO88591 character set and
cannot be floating-point data columns.
Index Keys
An index is stored in a key-sequenced file. There is always a one-to-one
correspondence between index rows and base table rows.
SQL/MP Index Keys
Each row in an SQL/MP index contains:
•
A two-byte column called the “keytag” column
•
The columns specified in the CREATE INDEX statement
•
The primary key of the underlying table (the user-defined primary key, the
SYSKEY, or combination of the clustering key and the SYSKEY)
For a unique index, the primary key of the index is composed of the first two of these
items. The primary key of the index cannot exceed 255 bytes, but the entire row
(including the primary key of the index) can contain up to 510 bytes.
For a nonunique index, the primary key of the index is composed of all three items.
The primary key cannot exceed 255 bytes. Because the primary key includes all the
columns in the table, each row is also limited to 255 bytes.
For varying-length character columns, the length referred to in these byte limits is the
defined column length, not the stored length. (The stored length is the expanded
length, which includes two extra bytes for storing the data length of the item.)
The keytag value must be unique among indexes for the table; you can specify it when
you create the index with the CREATE INDEX statement, or you can allow the system
to generate it for you. (System-generated keytags are sequential numbers, beginning
with one. User-specified keytag values can be either two bytes of character data or a
SMALLINT UNSIGNED value in the range 1 through 65535. The keytag value for the
primary key is 0.)
For more information, see the SQL/MP Reference Manual and the SQL/MX Query
Guide.
SQL/MX Index Keys
Each row in an SQL/MX index contains:
•
The columns specified in the CREATE INDEX statement
•
The clustering (primary) key of the underlying table (the user-defined clustering
key, the SYSKEY, or combination of the clustering key and the SYSKEY)