CREATE INDEX (SQL) - SQL Language Reference - Developer

CREATE INDEX

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

Syntax


CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON object_name ( column1 [ ASC | DESC ] [ , column2 [ ASC | DESC ] , ... ] )
[ INCLUDE ( column3 [ , column4 ] ) ]
[ WITH ( options ) ]
[ ON filegroup ]
[ ; ]

Description

An index is a data structure that improves the speed of data retrieval operations on a database table. It works by creating a pointer structure that the database search engine can use to traverse the data more quickly than scanning the entire table.

When you create an index, you specify:

Whether the index should be UNIQUE. A unique index ensures that no two rows have the same index key value.
Whether the index is CLUSTERED or NONCLUSTERED. A clustered index defines the physical order of the data rows in the table. A table can have only one clustered index. A nonclustered index is a separate structure from the data rows and contains pointers to the data rows.
The name of the index.
The table or view on which to create the index.
The column(s) to include in the index key, with optional sorting order (ascending or descending).
Optional INCLUDE columns for nonclustered indexes, which can improve query performance by including nonkey columns in the leaf level of the index.
Optional WITH options to configure index properties like fill factor, padding, and more.
The filegroup where the index will be stored.

Parameters

Parameter	Description
`UNIQUE`	Specifies that the index enforces uniqueness for the key values. No duplicate key values are allowed.
`CLUSTERED`	Specifies a clustered index. The leaf nodes of the clustered index contain the actual data rows of the table, ordered according to the index key.
`NONCLUSTERED`	Specifies a nonclustered index. The leaf nodes of a nonclustered index contain pointers to the data rows.
`index_name`	The name of the index to create. Must be unique within the schema.
`object_name`	The name of the table or view on which to create the index.
`column1 [ ASC \| DESC ]`	The column to be included in the index key. `ASC` specifies ascending order; `DESC` specifies descending order.
`INCLUDE ( columnN )`	Specifies additional columns to store in the leaf level of the nonclustered index. These columns are not part of the index key but can be used to satisfy queries without needing to access the base table.
`WITH ( options )`	Specifies index options such as `FILLFACTOR`, `PAD_INDEX`, `IGNORE_DUP_KEY`, etc.
`ON filegroup`	Specifies the filegroup for the index. If omitted, the index is created on the default filegroup.

Examples

Example 1: Creating a simple nonclustered index

This example creates a nonclustered index named IX_CustomerID on the CustomerID column of the Customers table.


CREATE NONCLUSTERED INDEX IX_CustomerID
ON Customers ( CustomerID ASC );

Example 2: Creating a unique clustered index

This example creates a unique clustered index named PK_Products on the ProductID column of the Products table. Since it's a clustered index, it's typically used for the primary key.


CREATE UNIQUE CLUSTERED INDEX PK_Products
ON Products ( ProductID );

Example 3: Creating a composite nonclustered index with INCLUDE columns

This example creates a nonclustered index named IX_OrderDate_ShipDate on the OrderDate and ShipDate columns, including TotalAmount for faster retrieval.


CREATE NONCLUSTERED INDEX IX_OrderDate_ShipDate
ON Orders ( OrderDate DESC, ShipDate ASC )
INCLUDE ( TotalAmount );

MSDN Documentation