Advertisements
Clustered index vs Non clustered index structure
Clustered and Non clustered indexes are stored in a 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 at the 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 have clustered index it’s called “Heap”
Non Clustered Index
- Non clustered indexes are separate storage. (I.e. original table and an index stored separately)
- The 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.
6 Comments
Anonymous
hi
Nice explanation…
But, In Clustered Index (4th Point) – has some Changes to be done…
CLUSTERED Index will be created by default When you create a PRIMARY KEY on that table – "Only If the table already doesn't have CLUSTERED INDEX".
If the table already have any CLUSTERED Index then, The PRIMARY KEY will be created as a NON-CLUSTERED Primary….
Anonymous
The changes to be done in "3rd Point" on Clustered Index part.. Not in 4th point… Thankx
muthukkumaran
Thanks for the information.(May i know who is this)
Pandian Sathappan
Hi
The comment posted by me… Kindly change the point – ASAP
muthukkumaran
Thanks pandian.
You r right.(That's additional information)
Penni Lockerby
I should say, youve got one of the most effective blogs Ive noticed in a long time. What I wouldnt give to be able to produce a blog thats as interesting as this. I guess Ill just need to maintain reading yours and hope that one day I can write on a topic with as substantially expertise as youve got on this one!