Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

cluster index and non-clustered index Expand / Collapse
Posted Sunday, January 20, 2013 9:10 AM


Group: General Forum Members
Last Login: Tuesday, November 1, 2016 7:57 PM
Points: 18, Visits: 264
can any body answer this question ? In what cases developer use cluster and non cluster index .
suppose 40,000 rows are there in table .In that case which index will it use ?
i have great confusion to know this concept .i have searched so many blogs ,msdn sites bt i couldn't knw the difference how to correlate.
like cluster index,non cluster ,heap
Pls answer this [size="6"][/size]
Post #1409287
Posted Sunday, January 20, 2013 11:21 AM



Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 45,613, Visits: 44,143
Duplicate post. no replies to this thread please. Direct replies to

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1409294
Posted Monday, January 21, 2013 7:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 6, 2014 8:34 AM
Points: 239, Visits: 34
I am going to relate this topic to a development and writing of a book as most people at one time or another have thought about writing a book.
When initially thinking about a book you have a lot of ideas. They don’t necessarily flow in any order (although they may.) Since these ideas are popping into your head they are just a pile of ideas. Another term for pile is heap. They are just a heap of ideas. The ideas themselves are in no specific order but the orders they pop into your head are sequential.

This is what a heap table is. As each record is added to the table they are ordered one after the other. I won’t get into how they are referenced, just know they are added one after the other. So a heap table is all about when the records are added.

As you work on your outline for your book you begin to organize your thoughts. Instead of just writing your ideas down in a list you begin to order that list. You place ideas into sections then into chapters. You may even go further by creating a detailed outline based on time.

By physically ordering your ideas you are changing their position within the outline. This is what a clustered index does. If physically orders the data within the table. The last records added may become the first record in the table because it makes sense for it to be there. You book may be time based and thus time is the clustered index or your book my jump from time period to time period thus chapter becomes the clustered index. And just as you have a single outline, you can only have a single clustered index.

Finally, at the end of your book you are going to create an index. This index specifies where things (concepts) are in the book. Maybe your book is about people and there is someone who is references rarely yet you wish to let people know where he is referenced in the book. This is a non-clustered index. Where the person is referenced hops around. If you have a heap table then where you reference the person is not sequential. The first reference may be to when they die because that was what you’re first thought was. When they are born may be in the middle, again, because that was when you thought of it. And just as your book can have many concepts you can have many non-clustered indexes.

But if you have a clustered index then the first reference you find using a non-clustered index will be the first reference relative to the story line.

So, a heap table is all about the order of thought, or the order of entry into a table. A clustered index is all about the order of relevance and a non-clustered index is all about an idea contained within the context of the book or table.

Make sense?
Post #1409535
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse