Table space usage 1

Question

Post reply

Table space usage 1

Hugo Kornelis

SSC Guru

Points: 64790
More actions
November 8, 2011 at 11:21 pm

#251517

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/

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

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

Nice and easy question.

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

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 2

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

Håvard Mr or Mrs. 500 Points: 569 More actions · Answer 3

A good question!

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

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

whoop whoop! I learnt something 🙂

But I got the answer wrong 🙁

Paul White SSC Guru Points: 150468 More actions · Answer 5

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
All articles available on SQL.kiwi
@SQL_Kiwi

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 6

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

Lucy Dickinson SSC Enthusiast Points: 113 More actions · Answer 7

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

Lucy Dickinson
BI SQL Developer

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

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/

Paul White SSC Guru Points: 150468 More actions · Answer 9

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
All articles available on SQL.kiwi
@SQL_Kiwi

Rhox Hall of Fame Points: 3462 More actions · Answer 10

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 🙂

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 11

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!

Stuart Davies SSCoach Points: 19505 More actions · Answer 12

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

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/