Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Know Your SQL Objects Expand / Collapse
Author
Message
Posted Friday, May 13, 2011 4:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
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
Post #1108289
Posted Friday, May 13, 2011 4:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:29 AM
Points: 1,880, Visits: 3,460
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.
Post #1108291
Posted Friday, May 13, 2011 4:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1108301
Posted Friday, May 13, 2011 4:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:36 AM
Points: 1,415, Visits: 802
Tricky question today.
Post #1108302
Posted Friday, May 13, 2011 4:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:29 AM
Points: 1,880, Visits: 3,460
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'))


Post #1108316
Posted Friday, May 13, 2011 5:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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!
Post #1108334
Posted Friday, May 13, 2011 5:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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


Best Regards,
Chris Büttner
Post #1108342
Posted Friday, May 13, 2011 5:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 1,355, Visits: 491
I'm also caugth by the Extent owner case. Maybe next time you should write "might" instead of "can".
Post #1108344
Posted Friday, May 13, 2011 5:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:25 PM
Points: 1,850, Visits: 2,186
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
Post #1108350
Posted Friday, May 13, 2011 6:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 1,264, Visits: 3,594
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.
Post #1108360
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse