Thursday, September 22, 2011

Clustered Vs Nonclustered Index in SQL Server

Seeing this question asked so many times and at so many places, thought blogging about it would be a good idea.
The fundamental difference between a clustered index and a non-clustered index is in the structure. The leaf level of a cluster index is the base table data, sorted by the column on which the index is defined(known as cluster key). Think of clustered index as a phone book ordered by last name. Since the data in the table is sorted by the cluster key, there can only be ONE clustered index per table, though the index can include multiple columns(composite index).

On the other hand, the leaf level of a non-clustered index contains columns defined in the non-clustered key and a pointer back to the corresponding base table record. If the base table has a cluster index, then this pointer will be a cluster key of the base table record(logical RID). Also, in case the clustered index is non-unique, the non-clustered index will also include a column called uniquifier to locate a particular record if there are duplicate values. If the base table is a heap(no clustered index), then the pointer will be row ID of the corresponding base table record. Think of non-clustered index as the index at the back of a book. Unlike clustered indexes, you can have multiple non-clustered indexes(up to 249 in SQL Server 2005 and up to 999 in SQL server 2008) on a single table.


  1. Thanks Faisal, great post! Another good one that helped me: