Using CREATE INDEX
Go Up to Creating Indexes
The CREATE INDEX statement creates an index on one or more columns of a table. A single-column index searches only one column in response to a query, while a multi-column index searches one or more columns. Options specify:
- The sort order for the index.
- Whether duplicate values are allowed in the indexed column.
Use CREATE INDEX to improve speed of data access. For faster response to queries that require sorted values, use the index order that matches the query’s ORDER BY clause. Use an index for columns that appear in a WHERE clause to speed searching.
- Note: When working with encrypted columns, the MIN, MAX, BETWEEN and ORDER BY operations cannot use an index based on those fields due to the nature of the index key that is formed from the encrypted field value. So while the index is not useful for the above operations, it is still userful for equality matches and JOIN operations.
To improve index performance, use SET STATISTICS to recompute index selectivity, or rebuild the index by making it inactive, then active with sequential calls to ALTER INDEX. For more information about improving performance, see SET STATISTICS: Recomputing Index Selectivity.
The syntax for CREATE INDEX is:
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col ...]);