Creating an Index

Go Up to Creating Metadata

SQL provides CREATE INDEX for establishing user-defined database indexes. An index, based on one or more columns in a table, is used to speed data retrieval for queries that access those columns. The syntax for CREATE INDEX is:

EXEC SQL
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index> ON
table (col [, col ...]);

For example, the following statement defines an index, NAMEX, for the ­LAST_NAME and FIRST_NAME columns in the EMPLOYEE table:

EXEC SQL
CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);

Note: InterBase automatically generates system-level indexes when tables are defined using UNIQUE and PRIMARY KEY constraints. For more information about constraints, see the Data Definition Guide.

See the Language Reference Guide for more information about CREATE INDEX syntax.

Topics

• Preventing Duplicate Index Entries
• Specifying Index Sort Order