SQL Server: Non-Clustered Indexes

SQL Server provides two types of indexes, clustered and non-clustered indexes. Here you will learn non-clustered indexes.

The non-clustered index does not sort the data rows physically. It creates a separate key-value structure from the table data where the key contains the column values (on which a non-clustered index is declared) and each value contains a pointer to the data row that contains the actual value. It is similar to a textbook having an index at the back of the book with page numbers pointing to the actual information.

There can be 999 non-clustered indexes on a single table is 999.

When you create a Unique constraint, a unique non-clustered index is created on the table.

The syntax for creating a non-clustered index is similar to the clustered index. Just use the keyword "NONCLUSTERED" instead of "CLUSTERED". The following syntax is to create a new non-clustered index on a table.

CREATE NONCLUSTERED INDEX <index_name>
ON <table_name>(column)

For example, the following creates a non-clustered index on the Email column of the Employee table. Note that the Employee table has a primary key EmployeeID so that automatically creates a clustered index.

CREATE NONCLUSTERED INDEX NCI_Employee_Email
ON dbo.Employee(Email);

Create a Nonclustered Index using SSMS

You can create a non-clustered index using SQL Server Management Studio.

Step 1: Open SSMS. Connect to the database. In Object Explorer, expand the table where you want to create a non-clustered index.

Step 2: Right-click on the Indexes folder. Point to New Index and, select Non-Clustered index.., as shown below.

Step 3: In the New Index dialog box, on the General page, enter a name for the new index under Index Name.

Step 4: Click on the Add button under the Index Key Columns, as shown below.

Step 5: In the Select Columns from table dialog box, check the checkbox of the column(s) on which the non-clustered index should be created.

Step 6: Click Ok and save the table.

Thus, you can create non-clustered indexes.