CREATE INDEX command

This command creates an index on a base table.

Syntax

CREATE [UNIQUE] INDEX index_name
  ON table_name
   (column_name [ASC | DESC] [,column_name [ASC | DESC]]...)
Keyword Description
UNIQUE Creates a unique index
index_name Name of the index to create
table_name Name of the table for the index
column_name Column name to use in the index key. The index key is built using the columns in the order you specify in this list

Use

You can create indexes on initial tables that have yet to be used or referenced within a SELECT or INSERT statement. Once you use a table, you cannot go back to the table and create an index.

Example

CREATE UNIQUE INDEX STAFF_IX1 ON STAFF(ID)
CREATE INDEX STAFF_IX2 ON STAFF(DEPT,NAME)