It seems to be really hard to find accurate information about this. MSDN has a article about sparse columns, and which null percentage thresholds should be considered when using them. But the facts concerning default null storage space usage seem to be very difficult to come by.
Some sources claim that NULL values take no space whatsoever, but that would mean that sparse columns would be pointless in the first place. Some claim that only the null bitmap in the table definition adds a bit representing each nullable column, but that there's no further overhead. Some claim that fixed-length columns (char, int, bigint etc) actually use up the same amount of storage space regardless of whether the value is null or not.
So which is it, really?
Let's say I have a list of all the nullable columns in our DB with total rows in the table, and the number of NULL rows per each column and type. How would I calculate exactly how much space the NULL values are using now, so I could then predict exactly how much space is saved by altering the columns to sparse instead? I can add the 4 byte overhead to the non-null rows just fine, but it doesn't help when I have no idea what to do with the null rows?
For fixed length types such as
int NULL, it always use the length of the type (ie 4 bytes for int whether it is set to NULL or NOT NULL).For variable length types, it takes 0 bytes to store the NULL + 2 bytes in the variable length columns offset list. This is used to record where each variable length value is really stored in the row on the page.
In addition, the NULL or NOT NULL flag uses 1 bit for each columns. A table with 12 columns will use 12/8 bytes (=2 bytes NULL bitmap).
This link will give you a lot more information on the subject
Once you know the percentage of NULL, you can look at this link for an estimate of the potential gain. Sparse saves space on null value but will requieres more space for not null values.