Calculate Percentage of Null values over Non-Null values in SPARSE column


WHAT IS SPARSE COLUMN AND WHY DO WE USE IT?

• Designed to optimize storage space for columns with a large percentage of NULLs, 
• To designate a column as SPARSE, the column must allow NULLs. When a NULL is stored in a column designated as SPARSE, no storage space is consumed. However, non-NULL values require 4 bytes of storage space in addition to the normal space consumed by the data type.
• Unless you have a high enough percentage of rows containing a NULL to offset the increased storage required for non-NULL values, you should not designate a column as SPARSE.

You cannot apply the SPARSE property to:-

• Columns with the ROWGUIDCOL or IDENTITY property
• TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, or user-defi ned data types
• A VARBINARY(MAX) with the FILESTREAM property
• A computed column of a column with a rule or default bound to it
• Columns that are part of either a clustered index or a primary key
• A column within an ALTER TABLE statement

If a column is having Nchar type which means it stores 2 bytes. If we have 100 records than total space will be 2*100=200. If column is having SPARSE then 4bytes are added i.e 2+4=6bytes. To find the percentage of Null values over Non-null values.

Lets assume x null values and since null values do not allocate any space

X*0 + (100-x)* 2+4= 200