SQL in a Nutshell, 2nd Edition

Name

CREATE/ALTER INDEX Statement

Synopsis

Indexes are special objects built on top of tables that speed many data-manipulation operations, such as SELECT, UPDATE, and DELETE statements. The selectivity of a given WHERE clause and the available query plans the database query optimizer can choose from is usually based upon the quality of indexes that have been placed on the table in a given database.

The CREATE INDEX command is not a part of the ANSI SQL standard, and thus varies greatly among vendors.

Platform	Command
DB2	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

Common Vendor Syntax

CREATE [UNIQUE] INDEX index_name ON table_name
(column_name [, ...])

Keywords

CREATE [UNIQUE] INDEX index_name

Creates a new index named index_name in the current database and schema context. Since indexes are associated with specific tables (or sometimes views), the index_name need only be unique to the table it is dependent on. The UNIQUE keyword defines the index as a unique constraint for the table and disallows any duplicate values into the indexed column or columns of the table. (Refer to Section 2.4.)

ON table_name

Declares the pre-existing table that the index is associated with. The index is dependent upon the table. If the table is dropped, so is the index.

(column_name [, ...])

Defines one or more columns in the table that are indexed. The pointers derived from ...