How to Use Non-Clustered Indexes in SQL Server: A Detailed Guide

Non Clustered Index:

A nonclustered index is analogous to an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data. Since, the nonclustered index is stored separately from the actual data, a table can have more than one non clustered index, just like how a book can have an index by Chapters at the beginning and another index by common terms at the end.

In the index itself, the data is stored in an ascending or descending order of the index key, which doesn't in any way influence the storage of data in the table. 

The following SQL creates a Nonclustered index on the NAME column on Employee table:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">NonClustered</span><span class="pln"> </span><span class="typ">Index</span><span class="pln"> IX_Employee_Name
ON </span><span class="typ">Employee</span><span class="pun">(</span><span class="typ">Name</span><span class="pun">)</span><span class="pln">
</span>

Difference between Clustered and NonClustered Index:

• Only one clustered index per table, where as you can have more than one non clustered index
• Clustered index is faster than a non clustered index, because, the non-clustered index has to refer back to the table, if the selected column is not present in the index.
• Clustered index determines the storage order of rows in the table, and hence doesn't require additional disk space, but where as a Non Clustered index is stored seperately from the table, additional storage space is required.