Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Table space usage 1 Expand / Collapse
Author
Message
Posted Wednesday, November 9, 2011 3:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 3, 2014 3:11 AM
Points: 389, Visits: 306
palotaiarpad (11/9/2011)
The space used to store nullable fields is the only bottleneck. I missed it.


Same here .. nice question!
It's nice to know how SQL Server uses his space :)
Post #1202728
Posted Wednesday, November 9, 2011 3:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,256, Visits: 13,552
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!
Post #1202735
Posted Wednesday, November 9, 2011 3:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 3,060, Visits: 3,246
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 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
Post #1202739
Posted Wednesday, November 9, 2011 4:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 8,723, Visits: 9,273
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
Post #1202752
Posted Wednesday, November 9, 2011 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 5,975, Visits: 8,236
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
Post #1202757
Posted Wednesday, November 9, 2011 5:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
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
Post #1202759
Posted Wednesday, November 9, 2011 5:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1202761
Posted Wednesday, November 9, 2011 6:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:45 AM
Points: 766, Visits: 840
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
Post #1202778
Posted Wednesday, November 9, 2011 6:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
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?"


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1202779
Posted Wednesday, November 9, 2011 6:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 855, Visits: 862
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
Post #1202780
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse