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?

more ▼

asked Nov 10 '09 at 03:59 PM in Default

BrianDBA_Dancer gravatar image

11 2 2 2

Check the errata section for the book on the publisher's site. These guides are notorious for mistakes like this.
Nov 10 '09 at 04:45 PM Blackhawk-17

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.
Nov 10 '09 at 05:36 PM BrianDBA_Dancer
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Nov 11 '09 at 10:36 AM

Blackhawk-17 gravatar image

11.7k 28 30 35

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 11 '09 at 11:58 AM

nkav gravatar image

101 1 1 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 10 '09 at 04:37 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 10 '09 at 03:59 PM

Seen: 2417 times

Last Updated: Nov 10 '09 at 04:38 PM