May 13, 2011 at 7:45 am
Good question. keeps you on your toes.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
May 13, 2011 at 7:51 am
Brandie Tarvin (5/13/2011)
Don't believe me? Then I have an interesting exercise for you to try.....
I like that suggestion Brandie.
I would suggest submitting a question and then defending that question in this Forum.
Make sure you edit the question a lot and let others read it to determine that it is correctly worded before it is locked for submission. 😛
I think then you will better understand how many people can read the same documentation and interpet it in wildly differant ways.
One last thing I have to say for this QOTD and all like them.
I cringe imediately every time I read them question in email before I even see multiple answers.
I know at least one answer is going to involve some understanding of the BOL from an MS internal understanding Or it will involve not having that understanding. You almost have to know the author to determine which it will be.
I know I will learn something, but sometimes it ends up feeling like the learning you get from a foster parent and a belt. The ones with zero love and less respect. :smooooth:
May 13, 2011 at 7:54 am
Nils Gustav Stråbø (5/13/2011)
No. LOB data are text/image and the variable length max data types. ROW_OVERFLOW_DATA can contain any variable length column. Ref Table and Index OrganizationMy following example contains no LOB data types, there is only one row inserted in the table, but the row still span multiple pages. One IN_ROW_DATA page and multiple ROW_OVERFLOW_DATA pages. And let me repeat, none of the data types are LOB data. The question explicitly excludes LOB data type, not variable length data types.
Exactly. But it's not the data type of your fields/columns that's important here--the page type of the ROW_OVERFLOW_DATA allocation unit is text/image:
ROW_OVERFLOW_DATA [...] Pages are of type Text/Image.
And that's irrelevant of the data type of the overflowing fields (well, only variable fields with a certain size will actually grow beyond the 8 kB limit for a row).
Thank you SanDroid, for backing my point of view.
Regards,
Michael
May 13, 2011 at 8:00 am
SanDroid (5/13/2011)
Brandie Tarvin (5/13/2011)
Don't believe me? Then I have an interesting exercise for you to try.....I would suggest submitting a question and then defending that question in this Forum.
You mean like I'm doing?
SanDroid (5/13/2011)
Make sure you edit the question a lot and let others read it to determine that it is correctly worded before it is locked for submission. 😛
Um, no. Because it defeats the purpose of the QOTD. If everyone knew what the QOTD was going to be and all its potential answers BEFORE it became a QOTD, then it would be worthless as a learning tool. And that's what this is, a learning tool.
I stand by the question and the answers I added.
May 13, 2011 at 8:06 am
The 'quibbling' over this QotD is quite understandable.
I chose the first two options and the last two, because the question said to choose four options. I was told that I am wrong, and these four options are correct:
[font="Wingdings"]þ[/font] Extents can be owned by multiple objects
[font="Wingdings"]ý[/font] Extents can be owned by only one object
[font="Wingdings"]þ[/font] Data pages can be owned by only one object
[font="Wingdings"]þ[/font] A table row can be bigger than 8060 bytes if some of the datatypes are variable
I don't care what anyone says about how difficult it is to word questions unambiguously, those first two options seem contradictory - and besides, there is no need for two options:
[font="Wingdings"]þ[/font] An extent may be owned by one (or more) objects
Or:
[font="Wingdings"]þ[/font] An extent may be owned by a single object
[font="Wingdings"]þ[/font] An extent may be owned by more than one object
More importantly, the option 'Rows can span multiple pages (excluding LOB data)' is correct!
The only possible objection is that ROW_OVERFLOW allocation units count as LOB data (they are sometimes referred to as small LOBs, or SLOBs, but I doubt this was Brandie's point). As Nils demonstrated, 'ordinary' variable length columns can be pushed to ROW_OVERFLOW storage, thus storing a single row on more than one page - all without any LOB column data, as such.
This question had great potential.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 13, 2011 at 8:08 am
The question mention LOB data. I know it didn't say LOB_DATA, but in my mind I read LOB_DATA, which is not the same as ROW_OVERFLOW_DATA. A table with a varchar(max), text, image column will have their values stored in a LOB_DATA page even if they ore only a byte long. A varchar(8000) will be stored in the IN_ROW_DATA page if there is space, but moved to a ROW_OVERFLOW_DATA page if there is no space in the IN_ROW_DATA page. It is not moved to a LOB_DATA page. At least not that I'm aware of.
May 13, 2011 at 8:10 am
Thank you SQLkiwi, for backing up my points. I was starting to think that my knowledge was flawed.
May 13, 2011 at 8:21 am
Nils Gustav Stråbø (5/13/2011)
...A table with a varchar(max), text, image column will have their values stored in a LOB_DATA page even if they ore only a byte long.
Hi Nils,
Just a small point on that: VARCHAR(MAX) is stored exactly as VARCHAR(8000), by default, including possibly moving to a ROW_OVERFLOW allocation unit, until the value stored exceeds 8000 bytes. It is then stored using the same layout as the deprecated text data type.
The default behaviour of the LOB data types can be affected by the options 'text in row' and 'large value types out of row', which can be set using sp_tableoption.
So, by default, a VARCHAR(MAX) column containing a single character would be stored IN_ROW, whereas a TEXT column containing the same single character would be stored in LOB_DATA.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 13, 2011 at 8:22 am
Brandie Tarvin (5/13/2011)
Um, no. Because it defeats the purpose of the QOTD. If everyone knew what the QOTD was going to be and all its potential answers BEFORE it became a QOTD, then it would be worthless as a learning tool. And that's what this is, a learning tool.
I stand by the question and the answers I added.
Did not suggest in my wording or otherwise that a large group of people or everyone read and know the answer to the QOTD submission.
I was saying not to you, but to everyone; walk a mile in the QOTD author shoes and submit your own QOTD.
See how easy it is to do that without half the people that answer it missing the point or meaning.
Most of the learning tools I use have been audited. The best ones are.
May 13, 2011 at 8:24 am
SanDroid (5/13/2011)
Brandie Tarvin (5/13/2011)
Um, no. Because it defeats the purpose of the QOTD. If everyone knew what the QOTD was going to be and all its potential answers BEFORE it became a QOTD, then it would be worthless as a learning tool. And that's what this is, a learning tool.
I stand by the question and the answers I added.
Did not suggest in my wording or otherwise that a large group of people or everyone read and know the answer to the QOTD submission.
I was saying not to you, but to everyone; walk a mile in the QOTD author shoes and submit your own QOTD.
See how easy it is to do that without half the people that answer it missing the point or meaning.
Most of the learning tools I use have been audited. The best ones are.
Been burned too. Just because I had used the 2k8 syntax to insert test data (was in late 2010 by then).
The question had and still has great value.
May 13, 2011 at 8:36 am
Ha I got burned by the "Extents can be owned by only one object" as well on this. Looking at I think I read it as "Extents can be only owned by one object". Good question though as I had to bone up on the details on Books Online before answering. I really enjoy these QOD though. I've been learning a lot of things I wouldn't have otherwise ran into in my normal work.
May 13, 2011 at 8:40 am
Ninja's_RGR'us (5/13/2011)
SanDroid (5/13/2011)
Brandie Tarvin (5/13/2011)
Um, no. Because it defeats the purpose of the QOTD. If everyone knew what the QOTD was going to be and all its potential answers BEFORE it became a QOTD, then it would be worthless as a learning tool. And that's what this is, a learning tool.
I stand by the question and the answers I added.
Did not suggest in my wording or otherwise that a large group of people or everyone read and know the answer to the QOTD submission.
I was saying not to you, but to everyone; walk a mile in the QOTD author shoes and submit your own QOTD.
See how easy it is to do that without half the people that answer it missing the point or meaning.
Most of the learning tools I use have been audited. The best ones are.
Been burned too. Just because I had used the 2k8 syntax to insert test data (was in late 2010 by then).
The question had and still has great value.
Second question I submitted was in a hurry and did not notice that the final edit to make the answers and explinations clearer did not take. Then... to make it even more fun, Steve was on vacation and could not give the Points back.
May 13, 2011 at 8:42 am
OK, I went back and forth on "can" myself. I ended up leaving it, but I can see that it in fact may (no pun) have created more confusion.
So, I have awarded back points, and I changed the wording to "may". I also added back the "rows may span multiple pages" item. I was thinking LOB data when I reviewed this, but forgot about row overflow data. The physical structure/type of the pages is not necessarily relevant here, as Nils shows with his multiple varchar(8000) columns.
Brandie wrote a good question, but I can see the confusion, so I've edited it.
Comments encouraged on the changes.
May 13, 2011 at 8:47 am
Steve Jones - SSC Editor (5/13/2011)
OK, I went back and forth on "can" myself. I ended up leaving it, but I can see that it in fact may (no pun) have created more confusion.So, I have awarded back points, and I changed the wording to "may". I also added back the "rows may span multiple pages" item. I was thinking LOB data when I reviewed this, but forgot about row overflow data. The physical structure/type of the pages is not necessarily relevant here, as Nils shows with his multiple varchar(8000) columns.
Brandie wrote a good question, but I can see the confusion, so I've edited it.
Comments encouraged on the changes.
Still only 1 point? What's the reasoning behind the point valuation of the question? This seems a little over basic stuff, especially with the multiple choices.
Viewing 15 posts - 31 through 45 (of 84 total)
You must be logged in to reply to this topic. Login to reply