After going thru some tutorials on SQL Server 2008’s new feature “SPARSE COLUMN”, I have found that it doesn’t take any space if the column value is 0 or NULL but when there is a value, it takes 4 times the space a regular(non sparse) column holds.
If my understanding is correct, then why I will go for that at the time of database design?
And if I use that, then at what situation will I be?
Also out of curiosity, how does no space get reserved when a column is defined as sparse column (I mean to say, what is the internal implementation for that?)
Advertisement
Answer
A sparse column doesn’t use 4x the amount of space to store a value, it uses a (fixed) 4 extra bytes per non-null value. (As you’ve already stated, a NULL takes 0 space.)
-
So a non-null value stored in a bit column would be 1 bit + 4 bytes = 4.125 bytes. But if 99% of these are NULL, it is still a net savings.
-
A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that’s still a net savings.
So the “expected savings” depends strongly on what kind of column we’re talking about, and your estimate of what ratio will be null vs non-null. Variable width columns (varchars) are probably a little more difficult to predict accurately.
This Books Online Page has a table showing what percentage of different data types would need to be null for you to end up with a benefit.
So when should you use a Sparse Column? When you expect a significant percentage of the rows to have a NULL value. Some examples that come to mind:
- A “Order Return Date” column in an order table. You would hope that a very small percent of sales would result in returned products.
- A “4th Address” line in an Address table. Most mailing addresses, even if you need a Department name and a “Care Of” probably don’t need 4 separate lines.
- A “Suffix” column in a customer table. A fairly low percent of people have a “Jr.” or “III” or “Esquire” after their name.