SQL Create Index

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

CREATE INDEX Idx_Country
    ON Customer(Country)

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.

CREATE INDEX Idx_LastName
  ON Customer(LastName)

CREATE UNIQUE INDEX Idx_Phone
  ON Customer(Phone)

CREATE CLUSTERED INDEX Idx_Customer
  ON Customer(LastName DESC, FirstName DESC)

DROP INDEX Customer.Idx_CustomerName

• Our Sql CREATE Database Reference
• Our Sql CREATE Table Reference
• Our Sql CREATE View Reference