Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

For example: As we know that storing a null/non-null value in a DATETIME column takes 8 bytes. On the other hand Sparse DATETIME column takes no space for storing null value but storing a non-null value will take 12 bytes i.e. 4 bytes extra then normal DATETIME column.

At this moment the obvious question which arises in our mind is: Why 0 bytes for null value and 4 bytes extra for storing non-null value in a sparse column? Reason for this is, sparse column’s value is not stored together with normal columns in a row, instead they are stored at the end of each row as special structure named Sparse Vector. Sparse vector structure contains:

 [List of non-null Sparse Column Id’s – It takes 2 Bytes for each non-null Sparse column] + [List of Column Offsets — It takes 2 bytes for each non-null Sparse column].

So, defining columns with high density of null value as Sparse will result in huge space saving. As explained previously non-null value in the sparse column is stored in a complex structure, so reading non-null sparse column value will have slight performance overhead.

Let us understand the Sparse Column concept with below example.

Example:  In this example we will create two identical tables. Only difference between them is, in one table two columns are marked as Sparse. In both of these tables insert some 25k records and check the space utilization by these tables.

CREATE DATABASE SPARSEDEMO
GO
USE SPARSEDEMO
GO
CREATE TABLE SPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime sparse,
 col3 int sparse
)
CREATE TABLE NONSPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime,
 col3 int
)
GO
INSERT INTO SPARSECOLUMNTABLE VALUES(NULL,NULL)
INSERT INTO NONSPARSECOLUMNTABLE VALUES(NULL,NULL)
GO 25000

 Now check the space used by these tables by executing the below statements:

EXEC SP_Spaceused SPARSECOLUMNTABLE 
EXEC SP_Spaceused NONSPARSECOLUMNTABLE

Result:

name              rows        reserved data   index_size unused
SPARSECOLUMNTABLE 25000       392 KB  344 KB 8 KB       40 KB

name                 rows        reserved data   index_size unused
NONSPARSECOLUMNTABLE 25000       712 KB  656 KB 8 KB       48 KB

So, with above example it is clear that defining a column with high density of null values result’s in huge space saving.

Please correct me, if my understanding is wrong. Comments are always welcome.