difference between cluster index and noncluster index

# difference between cluster index and noncluster index

• Friday, July 15, 2011 7:40 AM

`hi`

```clustered index is physically stored
a table can have 1 clustered index

non clustered index is logically stored
a table can have 249 non clustred index```

how cluster index physically store a table and how noncluster index logically store a table

### All Replies

• Friday, July 15, 2011 8:01 AM

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

• Friday, July 15, 2011 8:06 AM

thanks ram

can you give me any example to explain phycally reorder table in cluster index and logically order in noncluster index

• Friday, July 15, 2011 8:22 AM

a good analogy for clustered and non-clustered is:

clustered index  - a dictionary, when you find a word in a dictionary, you find the meaning of the word with it. so if the word is the index key, and the meaning of the word is the data, the index and the data are stored in the same order.

non-clustered - an index of a book, when you find the word in the index, the index points you to a page number in the book and you have to go to that page.

• Friday, July 15, 2011 8:23 AM

Clustered index you can imagine a phone book and names as clutered indexe key as all names are sorted in sequencially... ie data is physically ordered

Where as non clusted indexe you can imagine with study books, you have index pages which direct you to particular page but data in the on pages are not ordered at all ... as data is not physically ordered but we have index to point to right page

hope it make sence

Please check this for requested detail http://www.sql-server-performance.com/2004/index-data-structures/

• Friday, July 15, 2011 8:53 AM

Nitin,

Clustered and Non clustered indexes are stored in B-tree structure .

Clustered Index

• Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order)
• A table has only one clustered index because, the original table stored in leaf level of the clustered index (Data pages).
• When you create a primary key by default clustered index will be created internally.(If the table has clustered index already then the non clustered index will be created internally)
• If the table does not has clustered index it’s called “Heap”

Non Clustered Index
• Non clustered indexes are separate storage. (I.e. original table and an index stored separately)
• Non clustered index does not enforce the logical order. The physical order of the rows is not the same as the index order.
• A table has 999 non clustered indexes in sql-2008, 249 non clustered indexes prior to 2008.
• When you create a unique key by default non clustered index will be created internally.

Muthukkumaran Kaliyamoorthy SQL DBA
Helping SQL DBAs and Developers >>>SqlserverBlogForum
• Friday, July 15, 2011 8:54 AM

Read the Gail's link which i have given. It'll give you clear picture.
Muthukkumaran Kaliyamoorthy SQL DBA
Helping SQL DBAs and Developers >>>SqlserverBlogForum
• Monday, July 18, 2011 5:33 AM

thank you!

thanks again.

• Monday, July 18, 2011 11:18 AM

You're welcome.
Muthukkumaran Kaliyamoorthy SQL DBA

Helping SQL DBAs and Developers >>>SqlserverBlogForum
• Wednesday, May 02, 2012 10:11 AM

very true....