SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013

I-9

Index Keys

An index is stored in a key-sequenced file. Each row in an 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 nonentity 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.)

There is always a one-to-one correspondence between index rows and base table

rows.

You should typically use random access to access index rows. Sequential access is

less efficient for large subsets of rows unless SQL can use index-only access. For

more information, see the SQL/MP Query Guide.