Table space usage 1

  • Comments posted to this topic are about the item Table space usage 1


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nice and easy question.

    I am very surprised that by now I am the only one (out of twenty) with the correct answer :blink:

  • The space used to store nullable fields is the only bottleneck. I missed it. :w00t:

  • A good question!

    It was not easy to get it right, I really learned something.

  • whoop whoop! I learnt something 🙂

    But I got the answer wrong 🙁

  • Good question. The explanation could have been improved very slightly:

    "SQL Server will use the same amount of storage for a column whether its value is NULL or a "real" value."

    This is true for fixed-length types (both DATETIME and NCHAR used in the example are fixed-length) but variable length columns e.g. of VARCHAR type use no room in the data row - the null bitmap is used to distinguish between NULL and an empty string.

  • Nice question.Hough difficult for me.Really learnt something thanks.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Really enjoyed this question .. pity I got it wrong but I have definitly learnt something!: 🙂

    Lucy Dickinson
    BI SQL Developer

  • Thanks, all, for the kind words!

    SQL Kiwi (11/9/2011)


    Good question. The explanation could have been improved very slightly:

    "SQL Server will use the same amount of storage for a column whether its value is NULL or a "real" value."

    This is true for fixed-length types (both DATETIME and NCHAR used in the example are fixed-length) but variable length columns e.g. of VARCHAR type use no room in the data row - the null bitmap is used to distinguish between NULL and an empty string.

    Good addition, Paul. For varying length data, my explanation is technically still correct, but indeed incomplete. For varying legth data, NULL takes the same amount as the shortest possible "real" value (which is teh empty string for varchar and nvarchar, and a zero-length binary string for varbinary).

    Advance warning as a special service for those who take the time to read the discussion - the next question in this series (scheduled to go live in about a week) will involve some heavy arithmetic!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (11/9/2011)


    Advance warning as a special service for those who take the time to read the discussion - the next question in this series (scheduled to go live in about a week) will involve some heavy arithmetic!

    Oh joy! :laugh:

  • palotaiarpad (11/9/2011)


    The space used to store nullable fields is the only bottleneck. I missed it. :w00t:

    Same here .. nice question!

    It's nice to know how SQL Server uses his space 🙂

  • fantastic question, hugo!! thanks!!!!

    I was wrong, because interpretation of the translation and also the rush to reply!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • lucydickinson (11/9/2011)


    Really enjoyed this question .. pity I got it wrong but I have definitly learnt something!: 🙂

    seconded - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Good question.

    The correct answer rate is still only 9%, which I find surprising. Mostly a very good explanation, too, but I have one small cavil:

    There is an index which supports the foreign key constraint, and indeed you are not permitted to create the foreign key constraint unless that index already exists when you try to create the foreign key; so it's wrong to say there is no such index. However, that index is not part of the table being discussed, but of the referenced table, and it's reasonable to assume that the question is about space for the table being discussed so that space for the index on the referenced table doesn't count, and thus this affects only the explanation and not the answer.

    Tom

  • L' Eomot Inversé (11/9/2011)


    Good question.

    The correct answer rate is still only 9%, which I find surprising.

    Thanks!

    I must say that the low rate of correct answers surprises me. I intended this question to be a relatively easy first question in a series (hence the "1" in the title). I have already submitted the second one, and I won't change that - but I'll have to rethink the difficulty level of the remaining planned questions (that I did not submit yet).

    Mostly a very good explanation, too, but I have one small cavil:

    There is an index which supports the foreign key constraint, and indeed you are not permitted to create the foreign key constraint unless that index already exists when you try to create the foreign key; so it's wrong to say there is no such index. However, that index is not part of the table being discussed, but of the referenced table, and it's reasonable to assume that the question is about space for the table being discussed so that space for the index on the referenced table doesn't count, and thus this affects only the explanation and not the answer.

    I think your remark about the explanation is a bit far-fetched - but I'll admit that one could interpret the explanation that way.

    Luckily, the text of my question explicitly includes "... used by the table are true". So it is not only, as you say, reasonable to assume that the question is about space for the table being discussed - it is explicitly stated!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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