Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:24 AM
Points: 12, Visits: 24
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 #1409286
Posted Sunday, January 20, 2013 11:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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 #1409295
Posted Sunday, January 20, 2013 9:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:24 AM
Points: 12, Visits: 24
Thanks gila for giving these links.
great use of these concepts
Post #1409345
Posted Monday, January 21, 2013 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 06, 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 #1409539
Posted Monday, January 21, 2013 7:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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 #1409542
Posted Monday, January 21, 2013 8:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 06, 2014 8:34 AM
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
Post #1409589
Posted Monday, January 21, 2013 9:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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 #1409597
Posted Monday, January 21, 2013 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 06, 2014 8:34 AM
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.
Post #1409603
Posted Monday, January 21, 2013 9:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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 #1409624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse