We all know sparse columns will save space for columns which have more null values and take more space to store not null values. Recently my colleague asked me reason behind this behavior. This is because of row format it's used to store sparse and non-sparse columns.

In case of sparse columns it stores the information in below format. Suppose, we declared 10 columns as sparse, and 5 columns in the row has not null values, then only those 5 columns details will be stored in below format.

Column Id Set : Sparse Columns which have not null values.
Column offset : relative position, where data starts 
Column values : Data of the columns,

In case of Non-sparse columns it stores the information like this. Suppose, we declared 10 columns as non-sparse, and 5 columns in a row has not null values, still all 10 columns offsets will be stored. So, it means though the column has null value, still its taking 2 bytes of space to store column offset information.

Column offset : relative position, where data starts for the sparse columns
Column values : Values of the sparse columns, which have not null values.

So, if you observe these both formats, it's clear that sparse columns will save space for columns which have null values and they will take more space for not null values (for storing Column Id Set)

Note: I didn't include fixed length columns format, while discussing non-sparse columns. They will not have any offset information. In case of non-sparse columns, it will take same storage space, whether they have NULL or NOT NULL values.

Published under: SQL Server Tips ·  ·  ·  ·