Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cluster index and non-clustered index


cluster index and non-clustered index

Author
Message
shreekanth.kavali
shreekanth.kavali
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 266
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]

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
shreekanth.kavali (1/20/2013)
can any body answer this question ? In what cases developer use cluster and non cluster index .


That's a little too vague to be answerable. Most likely a clustered index. Maybe one or more nonclustered indexes too.

suppose 40,000 rows are there in table .In that case which index will it use ?


No way to answer that. Depends on the query

Maybe take a read through these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/


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


shreekanth.kavali
shreekanth.kavali
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 266
Thanks gila for giving these links.
great use of these concepts
Scott Rankin
Scott Rankin
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
Scott Rankin (1/21/2013)
This is what a clustered index does. If physically orders the data within the table.


Logical order. Not physical. The index may be physically ordered by the clustered index, but it's not enforced.


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


Scott Rankin
Scott Rankin
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 34
As per BOL...

"Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. "

If the data rows are sorted in order then that is physical. Are you thinking about b-trees? In a clustered table the leaf pages contain the data, not just a pointer to the data. In a non-clustered index in a clustered table the index contains a pointer to the clustered value. In a heap table the non-clustered index has a pointer to File/Sector/Page.

Am I missing something here?


ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/c498fe0c-a0ff-4677-a419-31f0e7875b61.htm

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_0evalplan/html/26b28045-c3c2-465a-b564-bf2189e93fdc.htm
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
Yes, I know, books online is wrong. I really must get ahold of someone on the docs team and get this fixed.

It's trivial to prove, I'll leave that up to you if you like, but consider this:
Logical fragmentation measures how different the logical ordering of the index is from the physical ordering.
BoL:
Logical Fragmentation

This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.


If a clustered index was always ordered physically by the key, it would follow (from the definition of logical fragmentation) that a clustered index always has 0% logical fragmentation, which in reality it does not.

If the data rows are sorted in order then that is physical.


Logical order yes, not necessarily physical. The pages are linked by pointers that point to the next page in the logical order. The page does not have to be the next physically contiguous page.

Are you thinking about b-trees?


No.
Both clustered and nonclustered indexes are b-trees, both are sorted logically by their key columns and may also be physically sorted, though that is not guaranteed.

In a clustered table the leaf pages contain the data, not just a pointer to the data. In a non-clustered index in a clustered table the index contains a pointer to the clustered value. In a heap table the non-clustered index has a pointer to File/Sector/Page.


Almost right. File ID, Page Number, slot index comprises the RID for nonclustered indexes on a heap.


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


Scott Rankin
Scott Rankin
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 34
So you are getting way down into the heart of SQL Server internals here. I'd love to have a reference to where I can read about this. But, if I understand it correctly, the difference between logical and physical at this point is way beyond what the originator of the thread was worried about. He'll need to spend 5 more years working with it then take a SQL Server internals class to fully understand what you are saying.

By-the-way, you are further down into the internals than I am too. I believe you and the logical/physical nature of the indexes although I don't quite understand it.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
Scott Rankin (1/21/2013)
So you are getting way down into the heart of SQL Server internals here.


Not that deep really

But, if I understand it correctly, the difference between logical and physical at this point is way beyond what the originator of the thread was worried about.


Sure, but the 'clustered indexes are physically ordered by their keys' can result in some nasty misunderstandings of how indexes work and why. Classic one I see is people claiming that because the clustered index is physically ordered by the key, it's faster for range searches (in fact BoL claims that), whereas actually nonclustered indexes are faster for range searches.


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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search