Introduction

---

The SQL CREATE INDEX statement is used to create a secondary index for a table.

Syntax

---

CREATE [UNIQUE] [NOCASE]
INDEX [IF NOT EXISTS] index_name

ON table_reference

(column_name [ASC or ASCENDING | DESC or DESCENDING]
[, column_name...])
[COMPRESS DUPBYTE | TRAILBYTE | FULL | NONE]]
[NOKEYSTATS]

Use the CREATE INDEX statement to create a secondary index for an existing table. If index names contain embedded spaces they must be enclosed in double quotes ("") or square brackets ([]). Secondary indexes may be based on multiple columns.

UNIQUE Clause

---

Use the UNIQUE clause to create an index that raises an error if rows with duplicate column values are inserted. By default, indexes are not unique. The syntax is as follows:

UNIQUE

NOCASE Clause

---

The NOCASE clause specifies the that the secondary index should be sorted in case-insensitive order as opposed to the default of case-sensitive order. The syntax is as follows:

NOCASE

Columns Clause

---

The columns clause specifies a comma-separated list of columns that make up the secondary index, and optionally whether the columns should be sorted in ascending (default) or descending order. The syntax is as follows:

(column_name [[ASC |ASCENDING] | [DESC | DESCENDING]]
[, column_name...])

The column names specified here must conform to the column naming conventions for DBISAM's SQL and must have been defined earlier in the CREATE TABLE statement. Please see the Naming Conventions topic for more information.

COMPRESS Clause

---

The COMPRESS clause specifies the type of index key compression to use for the secondary index. The syntax is as follows:

COMPRESS DUPBYTE | TRAILBYTE | FULL | NONE

The DUPBYTE keyword specifies that duplicate-byte index key compression will be used, the TRAILBYTE keyword specifies that trailing-byte index key compression will be used, and the FULL keyword specifies that both duplicate-byte and trailing-byte index key compression will be used. The default index key compression is NONE. Please see the Index Compression topic for more information.

NOKEYSTATS Clause

---

The NOKEYSTATS clause specifies that the index being defined should not contain any statistics.. The syntax is as follows:

NOKEYSTATS

Under most circumstances you should not specify this clause. Not using the index statistics is only useful for very large tables where insert/update/delete performance is very important, and where it is acceptable to not have logical record numbers or statistics for optimizing filters and queries.

The following statement creates a multi-column secondary index that sorts in ascending order for the CustNo column and descending order for the SaleDate column:

CREATE INDEX CustDate
ON Orders (CustNo, SaleDate DESC) COMPRESS DUPBYTE

The following statement creates a unique, case-insensitive secondary index:

CREATE UNIQUE NOCASE INDEX "Last Name"
ON Employee (Last_Name) COMPRESS FULL

Please see the Adding and Deleting Indexes from a Table topic for more information on creating indexes.