Sparse column Part 1

Sparse ColumnsMSSQL 2008 has come with sparse columns,which is a solution for space taken by null columns. It is used when column has majority of null values. It actually takes no value for storing null values but it takes some more space for non null values.



So How much space is taken by sparse columns?

• It takes no space for null values.
• For non null values
  For fixed length and precision dependent it takes 4 extra bytes
  For variable length it takes 2 extra bytes

Lets try to under stand how it works
Table with non sparse columncreate table test
( id int ,
person varchar(10),
salary int )
Suppse we have 10,000 rows in a table , out of which only for 1000 person salary data is available.
Now if salary is not sparse then it will take about 10,000 * 4 = 40,000 byte of space.
Table with sparse columncreate table test
( id int ,
person varchar(10),
salary int sparse )
Now since its sparse non null int will take 4 bytes more .
so 1000 non null salary values will take 1000 * (4 + 4) = 8000 bytes.
So we can see here that it saves 32,000 bytes.
For a column with atleast 60 % null values we can think to make it a sparse column.

How to alter column to make it sparse columnAlter table test
alter column salary add sparse
How to make a sparse column to non sparse columnalter table test
alter column salary drop sparse