Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Know Your SQL Objects


Know Your SQL Objects

Author
Message
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 1082
Nils Gustav Stråbø (5/13/2011)

"-Rows can span multiple pages (excluding LOB data)"
True. Row-overflow.


I guess this needs to be argued with Microsoft Hehe
BOL states that "Rows cannot span pages"; if the amount of data is too large to fit into 8060 bytes, one or more variable length columns are moved to pages in the ROW_OVERFLOW_ALLOCATION unit and a pointer is kept in the original row/page.
My personal understanding and logic would say "Yes, rows can span multiple pages", but Microsoft insists on that this is not the case. Perhaps due to the different types of "normal" pages and those in the ROW_OVERFLOW_ALLOCATION unit (http://msdn.microsoft.com/en-us/library/ms189051.aspx)--but that's just my assumption.
(But then, Brandie Tarvin explicitely excluded LOB data for this response option.)

Excellent question and thorough explanation, and since I came across the above BOL page a couple of days ago, managed to get it right. :-)

Thanks,
Michael
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
A row cannot span multiple pages of type IN_ROW_DATA, but it can certainly span multiple ROW_OVERFLOW_DATA and LOB_DATA pages. So when Brandie excluded LOB_DATA, there is still ROW_OVERFLOW_DATA which can allow a row to span multiple pages.
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 1082
Nils Gustav Stråbø (5/13/2011)
A row cannot span multiple pages of type IN_ROW_DATA, but it can certainly span multiple ROW_OVERFLOW_DATA and LOB_DATA pages. So when Brandie excluded LOB_DATA, there is still ROW_OVERFLOW_DATA which can allow a row to span multiple pages.


Both (LOB_DATA and ROW_OVERFLOW_DATA pages) are of the same type (text/image)--hence excluding LOB_DATA for me included ROW_OVERFLOW_DATA as well. As I've stated in my previous post, this needs to be argued with Microsoft (again, following my personal logic I fully agree with you--but Microsoft and BOL have a different view).
In my opinion Brandie did a fabulous job phrasing this QotD and eliminating all ambiguities if you adhere to BOL standards.

Regards,
Michael
paul s-306273
paul s-306273
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1919 Visits: 1052
Tricky question today.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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 Organization

ROW_OVERFLOW_DATA Allocation Unit

For every partition used by a table (heap or clustered table), index, or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page.

Text/Image pages in the ROW_OVERFLOW_DATA allocation unit are managed in the same way pages in the LOB_DATA allocation unit are managed. That is, the Text/Image pages are managed by a chain of IAM pages.

LOB_DATA Allocation Unit

When a table or index has one or more LOB data types, one LOB_DATA allocation unit per partition is allocated to manage the storage of that data. The LOB data types include text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined types.


My 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.
use tempdb
go

create table test(
c1 varchar(8000),
c2 varchar(8000),
c3 varchar(8000),
c4 varchar(8000),
c5 varchar(8000),
c6 varchar(8000),
c7 varchar(8000),
c8 varchar(8000),
c9 varchar(8000),
c10 varchar(8000),
c11 varchar(8000),
c12 varchar(8000)
)

insert into test(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
values(
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000),
replicate('a',8000)
)

select * from sys.allocation_units where container_id=(select partition_id from sys.partitions where object_id=object_id('test'))



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20927 Visits: 9671
Ya 1 point definitely underkill for this one.



Maybe Steve's MCM training has skewed his judgement a little bit... you're getting too good Stevie! Hehe
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
I got caught by the wording of Extents can be owned by only one object as well.
Maybe a language issue, but I interpreted it as the complementary answer to Extents can be owned by multiple objects.

With regards to Text data is stored on data pages, this is debatable. It depends on the options you have set up for your table (table option "text in row", http://msdn.microsoft.com/en-us/library/ms173530.aspx) and on the size of the data you are trying to store.
But I would not have included the Text data part at all in the QOTD, since this is deprecated and ancient :-P

Best Regards,

Chris Büttner
palotaiarpad
palotaiarpad
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1908 Visits: 753
I'm also caugth by the Extent owner case. Maybe next time you should write "might" instead of "can".
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
This question reminds me of my grad school and teaching days. Some people people feel that to make a challenging question, there has to be a semantic trick to the way the question is asked. This frequently occurs when the person asking the question is so sure of the answer that they fail to put themselves in the place of the person asked to answer the question. They don't see that the question is phrases in a possibly confusing manner. With a question like that, you're not testing subject knowledge, you're testing test taking skill. And, I might mention it places an extra burden on the nonnative speaker of the language - someone who is not as well equipped to ferret out the subtleties in the question.

Or, these questions can be developed by someone with a (hidden or not) sadistic streak that takes joy in the semantic trick itself.

To bad, as this has marred an otherwise good question that forced me to learn something I didn't know. Always good.

Disclaimer: The above is motivated by having only got 3 of 4 correct.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
calvo
calvo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 3965
John Mitchell-245523 (5/13/2011)
... But, as has been pointed out, it's the discussion and what you learn that's important, not the points.

John

Says you! You can buy tons of things with your points. From airline upgrades and stores to pork chop flingers and time warps. If you don't want your points, give them to me please, I almost have enough for the "I'm a DBA" button.
source

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search