O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE/ALTER INDEX Statement

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 are usually based upon the quality of the 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 its syntax varies greatly among vendors.

Platform

Command

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 "Constraints.")

table_name

Declares the pre-existing table with which the index is associated. 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 the indexed column or columns enable the database ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required