January 20, 2013 at 9:10 am
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]
January 20, 2013 at 11:26 am
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
January 20, 2013 at 9:39 pm
Thanks gila for giving these links.
great use of these concepts
January 21, 2013 at 7:07 am
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?
January 21, 2013 at 7:16 am
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
January 21, 2013 at 8:40 am
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
January 21, 2013 at 9:01 am
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
January 21, 2013 at 9:15 am
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.
January 21, 2013 at 9:48 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply