January 20, 2007 at 11:49 pm
I keep going over and over and over; and i am also reading the artical at the following address..
http://msdn2.microsoft.com/en-us/library/ms177484.aspx
Can someone put some human wording to nonclustered indexes???
I have an understanding of clustered indexes but i am a little off when i am trying to piece together why a non clustered in the leaf layer points to the clustered index.. There is a hole of important information that i am missing some where..
Can someone put some dick and jain wording to this so i can move forward..
Thanks a million!
Erik
Dam again!
January 21, 2007 at 12:49 am
Non clusterd index is point to clustered index if there is a clustered in on the table becuase of fast retrieval... navigation from level to another done by the key values...data is stored in an order based on clustered index key.. in case of heap table (without clustered index ) non clustered indexes are pointed to data pages.
http://msdn2.microsoft.com/en-us/library/aa933130(SQL.80).aspx
MohammedU
Microsoft SQL Server MVP
January 21, 2007 at 8:26 am
Once again;
Can someone please give me an human expatiation about this..
I stated that i have read a lot; therefore, i am needing to bring this together with someone’s help..
HELP!
Erik
Dam again!
January 22, 2007 at 12:10 am
OK, my take on it is this:
The non-clustered index has to point to something, right? It needs to either point directly to the file/page/row where the data lives - i.e. the row ID (RID) - or to some other intermediate index like the clustered index. Agree so far?
Non-clustered indexes on a heap table point to the data using the RID, because there's no alternative. So, why do others use the clustered index instead of the more direct page address? Wouldn't the RID be shorter / more efficient? Probably, but it's probably also more fragile / subject to change whenever data gets shifted around (because of page splits, etc.).
Not sure if that helps (or even if that's entirely accurate) but that's what makes sense to me.
Am looking forward to others' views.
Stephen
January 22, 2007 at 4:34 am
A non clustered index only has in it the values of the columns contained within the index. It dosn't have the rest of the row. If ta query is done that uses the index, but requires data that isn't contained in the index, the data has to be fetched from the actual row (the leaf level of the clustered index)
For a fairly good analogy, think of a book.
With a book, the page number can be considered the clustered index. It defines the physical order of the data, and the leaf level (ie the page of the book) is the data.
A non-clustered index would be the index at the back of the book. It has the keyword (the index key) and the page number(s) (the cluster key)
I open a book that I have and look up the term 'mutex' (doing a non-clustered index seek) The index tells me that the information I want is on page 94. I then do a lookup to the clustered index to page 94 to find out what it says about mutex.
Does that help at all?
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 22, 2007 at 4:46 am
Non-clustered indexes on a heap table point to the data using the RID, because there's no alternative. So, why do others use the clustered index instead of the more direct page address? Wouldn't the RID be shorter / more efficient? Probably, but it's probably also more fragile / subject to change whenever data gets shifted around (because of page splits, etc.).
Only heaps have a RID. Once a clustered index is placed on a table, it no longer has a RID, rather using the clustering key as a row identifier. a RID isn't a page address, it's a row identifier.
On a heap, a RID never changes. If data gets shifted off page (usually because the row has become too large for a page) you get a forwarding pointer. The RID points to the original location of the row, however the row isn't there, there's just a pointer to the actual location of the row.
You don't get page splits with a heap. A page split only occurs when data is stored in a particular order and a row needs to be inserted in the middle. That's only possible if you have something defining the physical storage order, ie a clustered index.
Forwarding pointers can give serious performance issues if you have too many, as SQL has to honour them as soon as it sees them. Say SQL's doing a scan of a heap that has 5 pages.. It reads page 1, then page 2. On page 2 there's a forwarding pointer to page 5. SQL then reads page 5. Then it goes back and reads page 3. Say there are two forwarding pointers on page 3, both pointing at page 5. SQl reads page 5 twice (once for each row). Then page 4 is read, which also containes a forwarding pointer to page 5, requiring another read of page 5, and finally page 5 is read as the last page in the table.
Net result, 5 pages, 9 reads. This is a bit of an extreme case, but it can easily happen with 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 23, 2007 at 3:58 am
January 26, 2007 at 9:37 am
Thank you very much for your help....
This helps out a lot!
Erik
Dam again!
January 26, 2007 at 9:54 pm
I know this question may be a little out their; but is there a way to know how the columns are laid out in the rows..
Is there a way to get a visual on columns information at the level of the datapages and heap?
Dam again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply