November 8, 2011 at 11:21 pm
Comments posted to this topic are about the item Table space usage 1
November 8, 2011 at 11:37 pm
Nice and easy question.
I am very surprised that by now I am the only one (out of twenty) with the correct answer :blink:
November 9, 2011 at 12:25 am
The space used to store nullable fields is the only bottleneck. I missed it. :w00t:
November 9, 2011 at 12:49 am
A good question!
It was not easy to get it right, I really learned something.
November 9, 2011 at 1:08 am
whoop whoop! I learnt something 🙂
But I got the answer wrong 🙁
November 9, 2011 at 1:26 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2011 at 1:51 am
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
November 9, 2011 at 2:02 am
Really enjoyed this question .. pity I got it wrong but I have definitly learnt something!: 🙂
Lucy Dickinson
BI SQL Developer
November 9, 2011 at 2:05 am
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!
November 9, 2011 at 2:39 am
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2011 at 3:15 am
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 🙂
November 9, 2011 at 3:39 am
fantastic question, hugo!! thanks!!!!
I was wrong, because interpretation of the translation and also the rush to reply!
November 9, 2011 at 3:44 am
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
November 9, 2011 at 4:45 am
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
November 9, 2011 at 5:07 am
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!
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply