Using Sparse Columns

The Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. When a value in a sparse column is NULL for any row in the table, the value requires no additional storage space. In this way, sparse columns are optimized for null values and reduce space requirements for null values. However, sparse columns increase the overhead required to retrieve non-null values. They also increase the storage space required for fixed-length data types.

Sparse columns are especially appropriate for filtered indexes because sparse columns have many null values. With filtered indexes on sparse columns, the index size is reduced because only the populated values are indexed and this can increase index and search efficiency. INSERT, UPDATE, and DELETE statements can reference sparse columns by name. You also can view and work with all the sparse columns of a table that are combined into a single XML column called a column set.