SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-6
Key-Sequenced File Structure
key-sequenced table in which the index columns make up the primary key. By using
index keys to support a unique index, you can improve performance for queries.
For key level consideration, indexes always have contiguous columns because the
order of the columns in the CREATE INDEX statement defining the index applies
rather than the position of the columns in the underlying table. For indexes, the
columns of the primary key or clustering key of the underlying table are included in the
index key. For information about index keys, see the SQL/MP Reference Manual.
Key-Sequenced File Structure
Key-sequenced files store variable-length rows (records) that contain a primary key.
New rows are stored in sequence by primary key value. A user performing update
operations can update or delete rows and can lengthen or shorten values in a varying-
length column (VARCHAR, NCHAR VARYING), provided the column is not part of a
primary key or clustering key.
Rows are stored in a key-sequenced file logically in ascending or descending order,
according to their primary-key values in conjunction with the ASCENDING or
DESCENDING specification in the CREATE TABLE statement.
Defining a Primary Key
Each key-sequenced file can have only one primary key: a user-defined primary key, a
system-generated SYSKEY column, or a user-defined clustering key concatenated
with a system-generated SYSKEY column.
You typically create key-sequenced tables with user-defined keys, rather than using
the system-defined SYSKEY. To specify a unique user-defined primary key, indicate
particular columns with the PRIMARY KEY clause. Alternatively, you can specify a
nonunique clustering key with the CLUSTERING KEY clause. With a primary or
clustering key, you can achieve a naturally sorted order for the table that
accommodates the most commonly used path to the data.
Before deciding which type of primary key to use, carefully consider the type and
usefulness of the naturally sorted order of the base table, including points about key
types:
•
At most, only one primary key or one clustering key can be defined for a particular
table; a table cannot have both a primary key and a clustering key. The key can
consist of more than one column, not necessarily adjacent to each other.
•
A table with a primary key or a clustering key can be partitioned.
•
The columns used as a primary key or clustering key cannot be defined to allow
null values. If you do not specify NOT NULL for a column in the PRIMARY KEY or
CLUSTERING KEY definition, the RDBMS defines the column to exclude null
values.
•
A value cannot be supplied for the SYSKEY column in an INSERT statement. This
rule applies to key-sequenced tables using either a SYSKEY column by itself or