Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012 > SQL Server – Differences between Clustered and Non-Clustered Indexes

SQL Server – Differences between Clustered and Non-Clustered Indexes

Both Clustered and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.

Below are some characteristics of Clustered Indexes and Nonclustered Indexes in SQL Server.

Clustered Index:

1. The leaf node of a Clustered Index contains data pages of the table on which it is created

2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key

3. If the table does not have Clustered Index it is referred to as a "Heap"

4. A Clustered Index always has Index Id of 1

5. A Table can have ONLY 1 Clustered Index

6. A Primary Key constraint creates a Clustered Index by default *

* A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key

 

Nonclustered Index:

1. The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row *

* When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created)

2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.

3. A table may not have any Nonclustered Indexes

4. Nonclustered Indexes have Index Id > 1

5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created

6. A Unique Key constraint created a Nonclustered Index by default *

* A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Jitendra
    April 20, 2013 at 5:26 pm

    thanx for clearify cluster and noncluster index…….

  2. April 23, 2013 at 1:34 pm

    Hi,

    there is a small error: “4. A Clustered Index always has Index Id of 0″ and “4. Nonclustered Indexes have Index Id > 0″

    The correct index id are:
    0 = heap
    1 = clustered
    >1 = non-clustered

    You can see this by running the following: SELECT * FROM sys.indexes

    Thanks
    Ian

    • April 25, 2013 at 1:26 pm

      Thanks for pointing that out Ian. I have updated the post…

    • chirag.patel19@gmail.com
      November 2, 2013 at 8:24 pm

      This answer is 100% perfect..

  3. Ganapathy Raman A
    May 6, 2013 at 12:56 pm

    can u please give sample example for clustered and non clustered index..

    Regards,
    Ganapathy A

  1. March 4, 2014 at 3:38 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 266 other followers

%d bloggers like this: