Reduce NULL value space in SQL server using SPARSE

 Microsoft introduced a new feature called SPARSE in SQL server 2008.This will help to reduce the space of column having high proportion of NULL values. It will help optimizing the SQL storage usage.

 

Where we can use this SPARSE?

              This can be only used column has a high percentage of NULL value in it. Let’s see an example, if you are storing data in fixed column length like int or bigint, once you save NULL in bigint its consume 8 bytes and most of the data are NULL in that column it should be huge storage space based on its data volume. So this can be resolved by creating SPARSE column.

 

CREATE TABLE Emp_table

( 

       ID int IDENTITY (1,1),

       First_Name VARCHAR(50) NULL,

       Last_Name VARCHAR(50) NULL,

       Emp_tagId BIGINT SPARSE NULL

)ON [PRIMARY]

 

From the above script, Emp_tagId is the SPARSE column created to eliminate storage space used by NULL, It will take zero storage space for NULL values

Where we can’t use SPARSE?

       If your column contain less NULL value or it is saving less than 50% of record NULL then it is not good to have use SPARSE because SPARS column consume extra 4 bytes than declared size,it is storing in special structure. Let assume in above example 70% Emp_tagId consists data and rest of them are non-NULL value then it will take total 12 bytes to save non-NULL values, Means 8 bytes for non-null value and 4 bytes for SPARSE.