question

BrianDBA_Dancer avatar image
BrianDBA_Dancer asked

SQL 2008 Prep Exam Question

Ok, I just started studying for the 70-432 exam (SQL2k8 implementation/maintenance.) I bought a book for it and one of the questions in the first chapter reads as follows:

You have a database table with a varchar(600) field in it. Most of the records in the table have a NULL value for this field. How can you save space?

A. Move the data into a second table.
B. Use sparse columns.
C. Install a third-party tool on the machine to compress the data.
D. Use the SQL Server 2008 Declarative Management Framework.

Now, I would've expected the answer to be (B) but the book is telling me (A) is the answer.

Can anyone tell me why this is the case?

sql-server-2008t-sqlbest-practice
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Check the errata section for the book on the publisher's site. These guides are notorious for mistakes like this.
1 Like 1 ·
BrianDBA_Dancer avatar image BrianDBA_Dancer commented ·
Below is the answer from the publisher's site. Thanks to Blackhawk for suggesting it. It doesn't clear anything up for me and makes no sense. :( The correct answer is A . Sparse columns will take up less space for varchar columns. Sparse columns will take up less space for most columns, but cannot be used for text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinary (max) with the FileStream attribute can be sparse. Answer C is incorrect; this is not the best approach. Answer D is incorrect, as the Declarative Management Framework will not save you space.
0 Likes 0 ·

3 Answers

·
Blackhawk-17 avatar image
Blackhawk-17 answered

It appears B is the correct answer and they forgot to include the rationale excluding A.

Actually, A would increase the size required if anything due to redundant row overhead introduced in adding a second table.

If the Publisher has a section for adding errata you may want to point out that their answer is not clear.

The thing about answers that these instructional books mess up is they make you research the answers deeper :)

Not exactly the intended result but one that does expand your understanding.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

That looks wrong to me. Maybe I'm just not thinking, but it strikes me that anything that would be solved by A would also be solved by an index rebuild. I am pretty sure the answer is B on that one.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nkav avatar image
nkav answered

This doesn't suprise me, the book is full of mistakes.

Check out the published corrections under this MSKB That's not a comprehensive list. My favorite was when the book discusses storage & refers to IDE hard drives in brackets as Integrated Development Environment.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.