Know Your SQL Objects

  • Comments posted to this topic are about the item Know Your SQL Objects

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Good question!!!

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Learn new thing.. thx

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Easy but tricky question,

    Srikant

  • Good question, but I thing the answer options are a little confusing.

    "-Extents can be owned by multiple objects"

    True, mixed extents.

    "-Rows can span multiple pages (excluding LOB data)"

    True. Row-overflow.

    "-Extents can be owned by only one object"

    False, since they can be a mixed extent (or even an extent that has not been allocated). Perhaps it's your use of the word "only" that confuses me, but what I read is "an extent must be owned by one, and only one, object."

    "-Data pages can be owned by multiple objects"

    False, obviously.

    "-Text data is stored on data pages."

    Do you mean text as in the datatype text, or as in text/strings? If you mean text as the text datatype, which makes this answer false, then please write so. If you mean text as in all data types that can store strings, then this answer is true.

    "-Data pages can be owned by only one object"

    True. One and only one object can use a data page. And when you compare the extent vs data page answers, you see why I'm confused.

    "-A table row can be bigger than 8060 bytes if some of the datatypes are variable"

    True. Again, row-overflow.

    I don't care about the points, but I think some of the options were a little vague.

    Or perhaps I'm just wrong 🙂

  • Nils Gustav Stråbø (5/13/2011)


    "-Extents can be owned by only one object"

    False, since they can be a mixed extent (or even an extent that has not been allocated). Perhaps it's your use of the word "only" that confuses me, but what I read is "an extent must be owned by one, and only one, object."

    The question uses the word can, not must. If an extent can be owned by multiple objects, it can certainly be owned by one object.

    Anyway, nice question, but only one point?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good question but the wording was a little ambiguous in my opinion. I learnt something though so I'm not complaining.

  • @Koen, yes I agree that I should not have interpreted the word "can" as "must", but unfortunately I did 🙁

    But the answer about text data is definitely ambiguous. Based on the "correct" answer it obviously related to the text datatype, not text data in general.

  • Good question, I could get 3 options right 🙂

    M&M

  • Koen Verbeeck (5/13/2011)


    Nils Gustav Stråbø (5/13/2011)


    "-Extents can be owned by only one object"

    False, since they can be a mixed extent (or even an extent that has not been allocated). Perhaps it's your use of the word "only" that confuses me, but what I read is "an extent must be owned by one, and only one, object."

    The question uses the word can, not must. If an extent can be owned by multiple objects, it can certainly be owned by one object.

    Anyway, nice question, but only one point?

    I'm with Nils on this. The sentence can be interpreted as "it is only possible for an extent to be owned by one object" or as "it is possible for an extent to be owned by only one object". I went for the former and so got it wrong. But, as has been pointed out, it's the discussion and what you learn that's important, not the points.

    John

  • 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

  • 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.

  • 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

  • Tricky question today.

  • 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'))

Viewing 15 posts - 1 through 15 (of 84 total)

You must be logged in to reply to this topic. Login to reply