Introduction

Creates a unique or non-unique index on one or more columns of a table or view. Indexes are used to speed up the retrieval of rows and enforce uniqueness on column values. SQL Server supports indexes on tables and indexed views.

Indexes can help queries perform faster by reducing the number of rows that must be examined to satisfy the query. They can also be used to enforce data integrity by ensuring that no duplicate values are entered in a column that has a unique index.

Syntax


CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON  ( column [ ASC | DESC ] [ ,...n ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON filegroup ]
    [;]

<table_or_view> ::= {
    table_or_view_name
}

<index_option> ::= {
        PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | IGNORE_DUP_KEY = { ON | OFF }
    | DROP_EXISTING = { ON | OFF }
    | ONLINE = { ON | OFF }
    | SORT_IN_TEMPDB = { ON | OFF }
    | DATA_COMPRESSION = { NONE | ROW | PAGE }
    | IGNORE_DISTINCT_COMP_VALUES = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_SOURCE = data_source
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY = { ON | OFF }
}

Arguments

Argument Description
UNIQUE Specifies that the index should enforce a unique constraint on the key columns. If any duplicate key values are inserted, SQL Server returns an error message. SQL Server allows duplicate values for non-unique indexes. By default, indexes are non-unique.
CLUSTERED Indicates that the index is a clustered index. A clustered index determines the physical order of data in the table. A table can have only one clustered index.
NONCLUSTERED Indicates that the index is a non-clustered index. Non-clustered indexes have a logical order that’s different from the physical order of the data. A table can have multiple non-clustered indexes.
index_name The name of the index. Index names must be unique within a table or view but do not need to be unique within a database.
<table_or_view> The name of the table or indexed view on which to create the index.
column Specifies the column or columns to be included in the index key. Multiple columns can be specified.
ASC Specifies ascending order for the specified column. This is the default.
DESC Specifies descending order for the specified column.
WITH ( ... ) Specifies index options. See Index Options below.
ON filegroup Specifies the filegroup on which the index is created. If not specified, the index is created on the default filegroup of the table or indexed view.

Index Options

Option Description
PAD_INDEX Specifies that the space available on leaf pages is used to store new entries at the middle level of the index. This can help when many duplicate keys exist.
FILLFACTOR Specifies how full SQL Server should make each page of the index when it is created or rebuilt. A fill factor of 100 percent means all pages will be full. A fill factor of 0 means SQL Server will make the pages as full as possible.
IGNORE_DUP_KEY Specifies an error report when a duplicate key value is inserted into a unique index. If IGNORE_DUP_KEY is ON, only the violating row is rejected. If IGNORE_DUP_KEY is OFF (default), the entire INSERT statement is rolled back. This option is only for unique indexes.
DROP_EXISTING Drops an existing index and rebuilds it. This can be used to rename an index, change its columns, or modify index options.
ONLINE Allows users to access the table or indexed view while the index is being created or rebuilt. Requires Enterprise Edition.
SORT_IN_TEMPDB Specifies that the intermediate sort runs required to create the index are performed in tempdb. This can reduce the time required to create an index.
DATA_COMPRESSION Specifies the data compression option for the table or view index. Options include NONE, ROW, or PAGE.
MAXDOP Overrides the max degree of parallelism setting for the index operation.
XML_COMPRESSION Specifies XML compression for XML indexes.

Permissions

Requires ALTER permission on the table or indexed view, or CREATE INDEX permission in the database.

Remarks

  • When you create a clustered index, the table is physically reorganized according to the order of the index key columns.
  • A non-clustered index creates a separate structure that points to the data rows.
  • Choosing the right columns for your index is crucial for performance. Consider columns that are frequently used in WHERE clauses or JOIN conditions.
  • For columns with low cardinality (few distinct values), indexes might not be as effective.
  • Unique indexes can help ensure data integrity.

Examples

Example 1: Creating a non-unique, non-clustered index


CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON Customers (LastName);

Example 2: Creating a unique, clustered index


CREATE UNIQUE CLUSTERED INDEX PK_ProductID
ON Products (ProductID);

Example 3: Creating a non-unique index with multiple columns and options


CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID
ON Orders (OrderDate DESC, CustomerID ASC)
WITH (FILLFACTOR = 80, PAD_INDEX = ON);

Example 4: Creating an index and replacing an existing one


CREATE NONCLUSTERED INDEX IX_Employee_Department
ON Employees (Department)
WITH (DROP_EXISTING = ON);

See Also