Create SQL Index Statement

2248 View    Jan 19 2020 12:56AM

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes; they are just used to speed up searches/queries.

It creates a relational index on a table or view also called a row store index because it is either a clustered or non clustered B-tree index. You can create a row store index before there is data in the table. Use a row store index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.


SQL Data Warehouse and Parallel Data Warehouse currently do not support unique constraints. Any examples referencing Unique Constraints are only applicable to SQL Server and SQL Database.






Besides storing the index key values, the leaf nodes also store row pointers to the data rows that contain the key values. These row pointers are also known as row locators.

If the underlying table is a clustered table, the row pointer is the clustered index key. In case the underlying table is a heap, the row pointer points to the row of the table.

Simple examples:

-- Create a nonclustered index on a table or view

CREATE INDEX i1 ON t1 (col1);


-- Create a clustered index on a table and use a 3-part name for the table

CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);


-- Syntax for SQL Server and Azure SQL Database

-- Create a nonclustered index with a unique constraint

-- on 3 columns and specify the sort order for each column

CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);



CREATE INDEX index_name

ON table_name (column1column2, ...);



ON table_name (column1, column2, ...);


We will use the sales.customers from the sample database for the demonstration.


 Using the SQL Server CREATE INDEX statement to create a nonclustered index for one column example








    city = 'Atwater';