SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / Table space usage 1 / Latest Posts

RE: Table space usage 1

Britt Cluff — Mon, 14 Nov 2011 19:24:53 GMT

Tough question. Should have been worth more than 2 points.

RE: Table space usage 1

Rob Schripsema — Thu, 10 Nov 2011 09:58:49 GMT

Excellent question. Understanding how the different data types are stored/handled in SQL is so helpful when creating EFFECIENT (not just working) database design. Thanks for the review -- and looking forward to the next in the series!

RE: Table space usage 1

DBA_Dom — Thu, 10 Nov 2011 08:50:21 GMT

I loved this question. I have just spent a few months reviewing this exact thing and how the data space is handled for different data types and nullable/non-nullable columns. As well as how clustered indexes (PK) become fragmented and at what rate based on a continually increasing PK.

RE: Table space usage 1

Koen Verbeeck — Thu, 10 Nov 2011 00:08:55 GMT

Great question, thanks Hugo.

RE: Table space usage 1

L' Eomot Inversé — Wed, 09 Nov 2011 13:19:07 GMT

[quote][b]jbreffni (11/9/2011)[/b][hr]I answered all the questions correctly but it still scored me as wrong!What's up with that?[/quote]That's odd. It was working earlier, showing the right answer as correct. And it is again now. Are you sure you ticked the boxes you meant to tick - and if so are you sure they were the right boxes? The thing is, if it was right before and is right now it seems very unlikely that it somehow changed to be wrong in time for you to suffer, and then changed back again.

RE: Table space usage 1

jbreffni — Wed, 09 Nov 2011 10:43:30 GMT

I answered all the questions correctly but it still scored me as wrong!What's up with that?

RE: Table space usage 1

BarbW — Wed, 09 Nov 2011 09:43:33 GMT

Good question, it really made me think...and I should have thought a little longer about it too :-D

RE: Table space usage 1

Cliff Jones — Wed, 09 Nov 2011 09:41:32 GMT

[quote][b]jeff.mason (11/9/2011)[/b][hr]Yeah, in my case I am so used to the indexes being created as a part of making the foreign key that I failed to notice that they weren't part of creating the table here. So that was what caused me to miss this one.[/quote]Yes, I almost tripped on that part as well. Good question though.

RE: Table space usage 1

SQLRNNR — Wed, 09 Nov 2011 09:36:36 GMT

Thanks for the question Hugo. This is a good question. A bit surprising that only 42 have answered correctly thus far.:ermm:

RE: Table space usage 1

OzYbOi d(-_-)b — Wed, 09 Nov 2011 09:35:53 GMT

Great question - look forward to the next one!

RE: Table space usage 1

Dave62 — Wed, 09 Nov 2011 08:55:46 GMT

[quote][b]Hugo Kornelis (11/9/2011)[/b][hr]... 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![/quote]Oh great, just when we start getting the question authors trained to not turn us into human SQL parsers. Now we have to train them not to turn us into human calculators!!!Just kidding. :-PKeep the great questions coming Hugo!

RE: Table space usage 1

Toreador — Wed, 09 Nov 2011 08:47:56 GMT

[quote][b]Hugo Kornelis (11/9/2011)[/b][hr]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![/quote]That still didn't stop me make the assumption that it was referring to the other table...:blush:

RE: Table space usage 1

Hugo Kornelis — Wed, 09 Nov 2011 08:39:25 GMT

[quote][b]Mike Dougherty-384281 (11/9/2011)[/b][hr]The number of ways of answering correctly: 1The number of ways of answering incorrectly: manyThe is no "partially correct" option. I might have complained about mixing 4 different ideas (and their negation) into the multiple-choice answer, but I thought "Grow up, these are professionals we're talking about" :) So I'm sharing this thought to possibly explain why results are skewed to 9% correct.I think there's also a psychological impact of 8 checkboxes - the answer seems simple enough, but then there's the second-guessing. After that first moment of hesitation, we're much less likely to check all (and only) the right boxes.Anyway, I admit I was guessing at the answers - but I think this is a great example of an edge case of SQL ServerCentral Question of the Day. Maybe if more questions were written this way we'd be more accustomed to it?[/quote]I know multiple-answers questions are not the most popular, but in this case there are four obviously mutually exclusive answer pairs, so you are basically facing four yes/no questions combined into one question. I thought that would not be overly hard. Apparently, I was wrong. :-) (It's also intrigueing that for none of the yes/no pairs, the answers given add up to 100% - obviously, there are people who overlook both the "choose 4" in the question and the fact that the options are mutually exclusive so that there [b]have[/b] to be four correct answers.FWIW, the second question in this series will be a single-answer one (albeit with no less than seven options to choose from), and I just submitted the third question as a simple Yes/No question. I'll keep the rest on hold until I know how the second one is received, so that I can adjust the difficulty as needed.

RE: Table space usage 1

KWymore — Wed, 09 Nov 2011 08:27:51 GMT

Nice question Hugo. I got 3 out of 4 right and learned something so I'm happy with that.

RE: Table space usage 1

Mike Dougherty-384281 — Wed, 09 Nov 2011 08:02:33 GMT

[quote][b]Hugo Kornelis (11/9/2011)[/b][hr]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).[/quote]The number of ways of answering correctly: 1The number of ways of answering incorrectly: manyThe is no "partially correct" option. I might have complained about mixing 4 different ideas (and their negation) into the multiple-choice answer, but I thought "Grow up, these are professionals we're talking about" :) So I'm sharing this thought to possibly explain why results are skewed to 9% correct.I think there's also a psychological impact of 8 checkboxes - the answer seems simple enough, but then there's the second-guessing. After that first moment of hesitation, we're much less likely to check all (and only) the right boxes.Anyway, I admit I was guessing at the answers - but I think this is a great example of an edge case of SQL ServerCentral Question of the Day. Maybe if more questions were written this way we'd be more accustomed to it?

RE: Table space usage 1

Stewart "Arturius" Campbell — Wed, 09 Nov 2011 07:04:48 GMT

Thanks for the question, HugoLooking forward to the next ones in the series...

RE: Table space usage 1

codebyo — Wed, 09 Nov 2011 06:53:06 GMT

[quote][b]Thomas Abraham (11/9/2011)[/b][hr]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:[/quote]Hahahaha, priceless! That's a smart teacher.I really enjoyed the question as it was not too hard as it seemed at first look but we needed to pay attention to detail and lots of text.Best regards,

RE: Table space usage 1

jeff.mason — Wed, 09 Nov 2011 06:43:40 GMT

Yeah, in my case I am so used to the indexes being created as a part of making the foreign key that I failed to notice that they weren't part of creating the table here. So that was what caused me to miss this one.

RE: Table space usage 1

Ernie Schlangen — Wed, 09 Nov 2011 06:41:37 GMT

As noted in a previous discussion, I (almost) always look up the subject of the QOTD, due to the interesting little tidbits that I gain on the side. I did so again today, but happily my initial answers were correct (which is not always the case). Looking back, I don't think this was too difficult a question, but I am one of those whose initial reaction to a multiple-answer multiple choice question can't be repeated here. But I will admit that one generally has to understand a multiple-answer multiple choice question in order to be correct rather than simply deducing the correct choice as is often possible with single-answer multiple choice questions. Good question! :satisfied:

RE: Table space usage 1

Mattrick — Wed, 09 Nov 2011 06:38:51 GMT

Great question. I got it wrong, because my reading comprehension needs to improve. I saw the foreign keys, the question asked about an index that supports those foreign keys, and I incorrectly made an surmised that the question was assuming the indexes were present, even though they are not implicitly created by the statement. The mistake is mine. You are one of the few question providers whose questions I can take a face value, and I needed to do that here.Thanks again, and I look forward to your next question.Matt

RE: Table space usage 1

ronmoses — Wed, 09 Nov 2011 06:09:32 GMT

The only thing that tripped me up was:[i]The amount of space used DOES depend on the fragmentation of the index that supports the FOREIGN KEY constraint. [/i]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

RE: Table space usage 1

Thomas Abraham — Wed, 09 Nov 2011 06:09:02 GMT

[quote][b]codebyo (11/9/2011)[/b][hr]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."[/quote]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:

RE: Table space usage 1

danielfountain — Wed, 09 Nov 2011 06:06:55 GMT

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

RE: Table space usage 1

Paul White — Wed, 09 Nov 2011 05:16:26 GMT

[quote][b]Hugo Kornelis (11/9/2011)[/b][hr]I must say that the low rate of correct answers surprises me.[/quote]The question [i]is[/i] reasonably difficult, Hugo. Multi-choice questions also tend to lower the success rate, historically speaking.[quote]Luckily, the text of my question explicitly includes "... used by [i]the table[/i] 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![/quote]FWIW I also assumed the bit about the 'FK index' related to the [i]referenced[/i] 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 :-)

RE: Table space usage 1

codebyo — Wed, 09 Nov 2011 05:10:47 GMT

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,

RE: Table space usage 1

Hugo Kornelis — Wed, 09 Nov 2011 05:07:36 GMT

[quote][b]L' Eomot Inversé (11/9/2011)[/b][hr]Good question.The correct answer rate is still only 9%, which I find surprising.[/quote]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).[quote]Mostly a very good explanation, too, but I have one small cavil:There [b]is[/b] 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.[/quote]I think your remark about the explanation is a bit far-fetched - but I'll admit that one [i]could[/i] interpret the explanation that way.Luckily, the text of my question explicitly includes "... used by [i]the table[/i] 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!

RE: Table space usage 1

L' Eomot Inversé — Wed, 09 Nov 2011 04:45:18 GMT

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 [b]is[/b] 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.

RE: Table space usage 1

Stuart Davies — Wed, 09 Nov 2011 03:44:54 GMT

[quote][b]lucydickinson (11/9/2011)[/b][hr]Really enjoyed this question .. pity I got it wrong but I have definitly learnt something!: :-)[/quote]seconded - thanks

RE: Table space usage 1

rfr.ferrari — Wed, 09 Nov 2011 03:39:52 GMT

fantastic question, hugo!! thanks!!!!I was wrong, because interpretation of the translation and also the rush to reply!

RE: Table space usage 1

Rhox — Wed, 09 Nov 2011 03:15:15 GMT

[quote][b]palotaiarpad (11/9/2011)[/b][hr]The space used to store nullable fields is the only bottleneck. I missed it. :w00t:[/quote]Same here .. nice question!It's nice to know how SQL Server uses his space :)

RE: Table space usage 1

Paul White — Wed, 09 Nov 2011 02:39:11 GMT

[quote][b]Hugo Kornelis (11/9/2011)[/b][hr]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![/quote]Oh joy! :laugh:

RE: Table space usage 1

Hugo Kornelis — Wed, 09 Nov 2011 02:05:02 GMT

Thanks, all, for the kind words![quote][b]SQL Kiwi (11/9/2011)[/b][hr]Good question. The explanation could have been improved very slightly:[i]"SQL Server will use the same amount of storage for a column whether its value is NULL or a "real" value."[/i]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.[/quote]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!

RE: Table space usage 1

Lucy Dickinson — Wed, 09 Nov 2011 02:02:10 GMT

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

RE: Table space usage 1

kapfundestanley — Wed, 09 Nov 2011 01:51:55 GMT

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

RE: Table space usage 1

Paul White — Wed, 09 Nov 2011 01:26:05 GMT

Good question. The explanation could have been improved very slightly:[i]"SQL Server will use the same amount of storage for a column whether its value is NULL or a "real" value."[/i]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.

RE: Table space usage 1

440692 I am just a number — Wed, 09 Nov 2011 01:08:48 GMT

whoop whoop! I learnt something :-)But I got the answer wrong :(

RE: Table space usage 1

Håvard — Wed, 09 Nov 2011 00:49:22 GMT

A good question!It was not easy to get it right, I really learned something.

RE: Table space usage 1

palotaiarpad — Wed, 09 Nov 2011 00:25:29 GMT

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

RE: Table space usage 1

vk-kirov — Tue, 08 Nov 2011 23:37:03 GMT

Nice and easy question.I am very surprised that by now I am the only one (out of twenty) with the correct answer :blink:[img]http://www.sqlservercentral.com/Forums/Attachment10159.aspx[/img]

Table space usage 1

Hugo Kornelis — Tue, 08 Nov 2011 23:20:59 GMT

Comments posted to this topic are about the item [B]Table space usage 1[/B]