SQL Sparse Columns

    SPARSE Column type is not new at all, It has been out since the version 2008. Sparse columns reduce the space requirements for null values, According to Microsoft space saved by sparse columns can be at least 20 percent to 40 percent.

You can not use SPARSE Column with the following data types.

Here is the list of restrictions for Sparse columns. Most of them make sense since we are dealing with NULL values.

• Sparse column must be nullable.
• Sparse column can not have a default value
• Sparse column cannot have the ROWGUIDCOL or IDENTITY properties.
• Sparse column can not be a computed column.
• A Sparse column can not be part of a clustered index or a unique primary key.
• Sparse columns are incompatible with data compression.
• Sparse column cannot be part of a user-defined table type.

I copied the following list from Microsoft to display how much space you can save if you use Sparse columns.

The NULL Percentage column indicates what percentage of the data must be NULL for a net space savings of 40%

It's pretty easy to create SPARSE column. All you need to do is, add SPARSE when you are creating your tables.

 So, how do we know if we saved any space?

 I have inserted 50000 records to CustomerLocations table. Also I have created the same table with no Sparse columns, and I named it CustomerLocations2.

Here is my results when I run sp_spaceused.

As you can see, the table with SPARSE columns(CustomerLocations) takes less space in the database.