O'Reilly logo

SQL in a Nutshell 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 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 ...

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