May 6, 2010 at 7:37 am
Re-read the MSDN article you'd attached in one of your earlier posts (Clustered Index Structures). Good to know. Thanks.
May 6, 2010 at 7:39 am
rjv_rnjn (5/6/2010)
I re-read the MSDN article as to why I thought it's physical order of arrangement.MSDN: http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
Because the clustered index dictates the physical storage order of the data in the table
And the option was:
Rows in a table are PHYSICALLY stored in the clustered index order
I am not sure I quite understand the difference.
The part in MSDN about rows being physically stored in the clustered index order is what threw me off as well. I think Hugo's explanation makes sense though.
May 6, 2010 at 8:19 am
What threw me off was "All nonclustered indexes include the clustered index columns in their index pages" . I thought this was incorrect because the clustered index columns are contained in the Leaf Node Data Pages and not every index page.
May 6, 2010 at 8:21 am
Wahoo! Got it right! I have to admit that I wasn't sure about the last three options, but I figured that since they were there, at least one of them had to be marked, so I guessed then used BOL to confirm before submitting (so maybe I cheated a little on that). Thanks Hugo (and Kalen, since without having read her books and class I never would have got this right).
Chad
May 6, 2010 at 8:35 am
dgabele (5/6/2010)
Good question, got tripped up on:"All nonclustered indexes include the clustered index columns in their index pages"
I thought this was not true for the mere point that a table does not have to have a clustered index - meaning that some nonclustered indexes only have the RID, therefore not ALL nonclustered indexes include clustered index columns...
Good intentions with the question, but the one check box was a crap shoot (which I guessed wrong on). Better wording could have been "When a table has a unique clustered index all nonclustered indexes include exactly the clustered index key in their index pages." (Notice the reduction in ambiguity.)
I choose false because not all nonclustered indexes include the clustered index columns in their index pages. IE Heaps, nonclustered indexes will include a pointer to the row.
From BOL
If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
May 6, 2010 at 8:54 am
rjv_rnjn (5/6/2010)
I re-read the MSDN article as to why I thought it's physical order of arrangement.MSDN: http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
Because the clustered index dictates the physical storage order of the data in the table
And the option was:
Rows in a table are PHYSICALLY stored in the clustered index order
I am not sure I quite understand the difference.
A good learn about the XML index, didn't know that.
This is basically a difference in terminology, and Microsoft's BOL entry is badly written.
So, we have a table, with pages, and a clustered index:
Page 1 contains a-b
Page 2 contains b-t
Page 3 contains u-w
Page 4 contains w-z
All are full.
The pages that make up the table are in "clustered index order".
However, on disk (where "physical" starts to actually matter, particularly on rotating or tape media), if we have 2 data files for this database/filegroup and the data's split evenly, even assuming the table isn't fragmented and the files aren't fragmented (pretty unlikely without good planning and maintenance, or luck): we're likely to have something like:
Page 1 on Sector 500
Page 2 on Sector 800
Page 3 on Sector 501
Page 4 on Sector 801
If we have fragmentation at either OS (which SQL Server cannot fix) or SQL Server level, we might have:
Page 1 on Sector 999
Page 2 on Sector 232
Page 3 on Sector 555
Page 4 on Sector 77
May 6, 2010 at 8:54 am
This question must be clearing up common confusions - only 2% got it right so far - thats the worst ratio of correct answers I've seen on a QOTD
May 6, 2010 at 8:58 am
Tom Brown (5/6/2010)
This question must be clearing up common confusions - only 2% got it right so far - thats the worst ratio of correct answers I've seen on a QOTD
Wow! That is good. Well done Hugo!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2010 at 8:59 am
Great question Hugo. A few options that can trip people up.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 9:01 am
Grant Fritchey (5/6/2010)
Tom Brown (5/6/2010)
This question must be clearing up common confusions - only 2% got it right so far - thats the worst ratio of correct answers I've seen on a QOTDWow! That is good. Well done Hugo!
I agree but it should have been worth 2 or 3 points, although I still got it wrong.
May 6, 2010 at 9:02 am
Great question Hugo even though I HATE select all that apply type of questions.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 6, 2010 at 9:03 am
The individual rows in a page at the leaf level of a clustered index are stored by the clustering key; hence, the physical order is at the page level. Page allocations for a table are not maintained by a clustering key.
May 6, 2010 at 10:22 am
Cliff Jones (5/6/2010)
Grant Fritchey (5/6/2010)
Tom Brown (5/6/2010)
This question must be clearing up common confusions - only 2% got it right so far - thats the worst ratio of correct answers I've seen on a QOTDWow! That is good. Well done Hugo!
I agree but it should have been worth 2 or 3 points, although I still got it wrong.
I was thinking the same thing. But the flip-side is that it is about a basic concept involving clustered indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 11:43 am
I missed it just because of the physical order part... They are physically stored in the clustered order, maybe just not contiguously/in sequence. 😉 The BOL docs don't help much.
May 6, 2010 at 11:58 am
I was just reading up on indexes the other day - so, with a little guesswork, I got it right. The only one that I wasn't sure about was the physical order. I was arguing this point with a consultant a few days ago. He stated that, for certain, the rows are arranged physcially on the disk exactly in the order of the clustered index. Since I caught him wrong on something else recently, I didn't pick that option and got it right. 🙂
Great question...
ps: It's a good thing I answered this later in the day... 😉
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply