CREATE INDEX (Transact-SQL)

Creates an index on one or more columns of a table or view.

Syntax


CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON  ( { column [ ASC | DESC ] } [ ,...n ] )
[ WITH (
    {
        PAD_INDEX = { ON | OFF }
        | FILLFACTOR = fillfactor
        | IGNORE_DUP_KEY = { ON | OFF }
        | DROP_EXISTING = { ON | OFF }
        | ONLINE = { ON | OFF }
        | MAXDOP = max_degree_of_parallelism
        | DATA_COMPRESSION = { NONE | ROW | PAGE }
        ...
    }
) ]
[ ON filegroup ]

Description

An index is a database object that is associated with a table or view, and is used to improve the performance of queries. Indexes contain keys derived from one or more columns in the table or view. These keys are stored in a structure (a B-tree) that allows the SQL Server query processor to find rows in the table or view more quickly than it could by scanning the entire table.

When you specify UNIQUE, SQL Server enforces that the index key contains no duplicate values. If you attempt to insert or update rows with duplicate key values, SQL Server will generate an error.

You can specify CLUSTERED or NONCLUSTERED. A clustered index determines the physical order of data in the table. A table can have only one clustered index. A nonclustered index is a separate structure from the data rows. A table can have multiple nonclustered indexes.

Parameters

UNIQUE: Specifies that the index should enforce uniqueness for all key values.
CLUSTERED: Indicates that the leaf nodes of the index contain the data pages of the table.
NONCLUSTERED: Indicates that the leaf nodes of the index contain pointers to the data rows.
index_name: The name of the index to create.
table_or_view_name: The name of the table or view on which to create the index.
column: The column or columns to include in the index key.
ASC: Sorts the index key in ascending order.
DESC: Sorts the index key in descending order.
WITH options: Specifies various options for index creation. See the official Microsoft documentation for a full list.
ON filegroup: Specifies the filegroup where the index will be stored.

Examples

Example 1: Create a Nonclustered Index

This example creates a nonclustered index named IX_Employees_LastName on the LastName column of the Employees table.


CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON Employees (LastName);

Example 2: Create a Unique Clustered Index

This example creates a unique clustered index named PK_Products_ProductID on the ProductID column of the Products table.


CREATE UNIQUE CLUSTERED INDEX PK_Products_ProductID
ON Products (ProductID);

Example 3: Create a Composite Index with Options

This example creates a nonclustered index on multiple columns and specifies options.


CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID
ON Orders (OrderDate DESC, CustomerID ASC)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON);

Important: Indexes can significantly improve query performance, but they also add overhead to data modification operations (INSERT, UPDATE, DELETE). Choose columns that are frequently used in WHERE clauses or JOIN conditions to create indexes.

For more detailed information and advanced options, please refer to the official Microsoft SQL Server documentation.