SQL in a Nutshell

Name

CREATE INDEX

Synopsis

Indexes are special objects built on top of tables that speed many data-manipulation operations, such as SELECT, UPDATE, and DELETE statements. When an index is created, the location and spread of values (called statistics) are built for the column that is indexed. The selectivity of a given WHERE clause is usually based upon the quality of indexes that have been placed on the table.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

The CREATE INDEX command varies greatly among vendors. One reason is that some DBMS vendors use the CREATE INDEX command to direct how the data in a given table is physically sorted and arranged on disk.

SQL99 Syntax and Description

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

All major vendors support composite indexes , also known as concatenated indexes . These indexes are used when two or more columns are best searched as a unit — for example, last name and first name.

Microsoft SQL Server Syntax and Variations

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name 
ON {table | view} 
                  (column [ASC | DESC] [,...n])
[WITH [PAD_INDEX]
   [[,] FILLFACTOR = fillfactor]
   [[,] IGNORE_DUP_KEY]
   [[,] DROP_EXISTING]
   [[,] STATISTICS_NORECOMPUTE] ]
[ON filegroup]
GO

Microsoft SQL Server has some important options. For example, ascending or descending indexes can be created on tables, as can indexes on views ...