Prior to SQL Server 2008 there was a lot of space wastage due to the storage of the NULL bitmaps for tracking NULL values in columns defined as NULL.SQL reserves the same space for columns as the data length irrespective of whether it contains NULL or NOT NULL values.For varied length columns it stores 2 byte offset to save the actual length pointer.
Before SQL Server 2008, the storage engine used to maintain a NULL bitmap to keep the NULL statuses of each column.So a NULL bitmap row on a table with 8 NULL columns would have 8 bits(1 byte)in the row ,a table of column 8 to 16 2 bytes so on and so forth.
With SQL 2008 a new property called as “Sparse Column” has been added which will maitain the NULL status in a structure called as “Space Vector”.The space vector will have 2 bytes for the column list,2 bytes for the column offset + actual data in case of not null values while statuses of the NULL columns will not be stored.The NULL bitmaps will still be used in case of NULL columns which are not defined as SPARSE columns and will use the same technique to track NULL columns.
Also you can use row compression so that the NULL columns wont reserve the data length for the fixed data types and the columns which has variable data types and are defined as NULL’s wont use the 2 byte offset.
Sparse columns are a good idea if you are planning for saving storage space at a much more granular level.So go ahead use this wondeful feature which can save you a hell lot of space without any overheads.
Regards
Sachin Nandanwar
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Nice Sachin – when we implement SPARSE columns, is there an overhead in retreiving non NULL values?
Amit,
The actual reason SPARSE column should be used is to minimise space requirement rather than fast retreival of NULL or NOT NULL values.