SQL - Sparse Columns and Column Sets

What are the Sparse Columns in SQL?

The SPARSE column property is a special, NULL-friendly column option - introduced with SQL Server 2008.  

Sparse column data storage is extremely efficient for NULL values. In fact, a NULL value requires no space at all in a sparse column - MSDN states that "when the column value is NULL for any row in the table, the values require no storage." However, the storage requirement for a non-NULL value is increased by up to 4 bytes when the SPARSE column property is used. Given that trade-off, Microsoft recommends not using sparse columns unless the percentage of NULL values in a column is high enough that a 20 percent to 40 percent storage savings gain would result. 

The ratio of NULLs to real values that would warrant implementing a sparse column differs for each data type.

Sparse Column Limitations 

A few things to keep in mind when considering using sparse columns: 

• Sparse columns cannot have default values, and must accept NULL values 

• A computed column cannot be SPARSE 

• Sparse columns do not support data compression 

• A sparse column cannot be a primary key 

Column Sets 

An interesting enhancement to a table that uses sparse columns is a column set. A sparse column set gathers all sparse columns into a new column that is similar to a derived or computed column, but with additional functionality – its data can be updated and selected from directly. A column set is calculated based on the sparse columns in a table, and it generates an untyped XML representation of all sparse columns and values (NULL or otherwise).