difference between cluster index and noncluster index

Question

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

thanks in advance

Answer 1

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.

Please check for more http://devtoolshed.com/content/clustered-index-vs-non-clustered-index-sql-server

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

thanks ram

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

Answer 3

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.

Answer 4

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/

 

---

http://uk.linkedin.com/in/ramjaddu

Answer 5

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

Answer 6

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

Answer 7

thank you!

it's very helpful for me!

thanks again.

Answer 8

You're welcome.

---

Muthukkumaran Kaliyamoorthy SQL DBA

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Answer 9

very true....