SQL Create Index

Indexes are used to speed up data retrieval from the database.

The CREATE INDEX command is used to create an index.

This will add a new index on one or more columns in a table.

To delete an index use DROP INDEX.

Example

#

This example creates an index on Country in the Customer table.

CREATE INDEX Idx_Country
    ON Customer(Country)

Using CREATE INDEX

#

An index maintains a list of one or more table columns for fast matching and retrieval.

The database engine looks for indexes first, before retrieving any data from the table.

An Implicit Index is one that is automatically created when a table is created.

Implicit Indexes are created when a table has a 1) primary key, or 2) unique constraints.

Syntax

Add an index to a table.

CREATE INDEX index-name
  ON table-name (column-name1, column-name2, ..)

Note: The order in which column-names are listed is important and should support the most common queries. For example, City, Country creates a different index from Country, City.

Add a unique index to the table.

CREATE UNIQUE INDEX index-name
  ON table-name (column-name1, column-name2, ..)

Note: A unique index does not allow duplicate values in the indexed column(s).

Add a clustered index to the table.

CREATE CLUSTERED INDEX index-name
  ON table-name (column-name1, column-name2, ..)

Note: The physical sort order of a clustered index row is the same as that of the table. A clustered index is very fast for range queries, but it degrades performance of INSERT, UPDATE, and DELETE operations. There can only be 1 clustered index per table. By default, the clustered index is placed on the primary key column(s).

Add a descending index to a table.

CREATE INDEX index-name
  ON table-name (column-name1 ASC|DESC, column-name2 ASC|DESC, ..)

Options are ASC and DESC for ascending and descending respectively. The default is ASC. Descending indexes perform better when the query requires rows to be returned in descending sort order.


More Examples

CREATE INDEX

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Add an index on LastName in the Customer table.
CREATE INDEX Idx_LastName
  ON Customer(LastName)
Result:  index created

CREATE UNIQUE INDEX

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Add a unique index on Phone in the Customer table.
CREATE UNIQUE INDEX Idx_Phone
  ON Customer(Phone)
Result:  index created

CREATE CLUSTERED DESCENDING INDEX

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Add a clustered index on LastName and FirstName in descending order.
CREATE CLUSTERED INDEX Idx_Customer
  ON Customer(LastName DESC, FirstName DESC)
Result:  index created

DROP INDEX

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Remove the Idx_Country index from the Customer table.
DROP INDEX Customer.Idx_CustomerName
Result:  index dropped

You may also like



Guides


vsn 3.1