SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table space usage 1


Table space usage 1

Author
Message
Rhox
Rhox
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1002 Visits: 480
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 Smile
rfr.ferrari
rfr.ferrari
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2141 Visits: 13639
fantastic question, hugo!! thanks!!!!

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


rfr.ferrari
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
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7353 Visits: 4817
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 Smart way to ask a questionThere 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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25689 Visits: 12494
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18591 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 1515
Wow, very elaborated question.
It made my brain hurt for a second.

I've got 3/4 options right.

I only missed one which should have been basic knowledge to me:

"The amount of space used DOES depend on the fragmentation of the index that supports the PRIMARY KEY constraint."

Thank you for the question.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34824 Visits: 11359
Hugo Kornelis (11/9/2011)
I must say that the low rate of correct answers surprises me.

The question is reasonably difficult, Hugo. Multi-choice questions also tend to lower the success rate, historically speaking.

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!

FWIW I also assumed the bit about the 'FK index' related to the referenced table, but the question was clear enough that that point did not lead me astray.

Last thing: I didn't see that this question was by you, so I approached it with some element of the usual 'I wonder what the author intended / knew' and selected the wrong answer for the NULL storage bit as a result. I blame an 'expectation hangover' after yesterday's question :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Daniel Fountain
Daniel Fountain
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 890
Sad i know but i was actually quite pleased to have got the right answer on a multi choice with such a low percentage of right answers. I had to really consider the options - really good question.

D
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3795 Visits: 2256
codebyo (11/9/2011)
Wow, very elaborated question.
It made my brain hurt for a second.

I've got 3/4 options right.

I only missed one which should have been basic knowledge to me:

"The amount of space used DOES depend on the fragmentation of the index that supports the PRIMARY KEY constraint."


Same here, including the part wrong. Forgot that primary key generates an index implicitly. Doh. Partial credit? :-)

Reminds me of my college days, when students would beg for "partial credit". One prof in the dept. would rant loudly, "Partial Credit! Partial Credit! Would you go to a doctor that always got partial credit?" w00t

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
ronmoses
ronmoses
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1674 Visits: 1011
The only thing that tripped me up was:

The amount of space used DOES depend on the fragmentation of the index that supports the FOREIGN KEY constraint.

I took this to imply that such an index exists. "A-ha! There is no such an index!" is a bit sneaky but fair, I suppose. You did provide the entire table creation script so I can't complain.

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search