Sparse Columns in SQL Server 2008

Sparse Columns are a new feature introduced in SQL Server 2008. Sparse columns are ordinary columns that have an optimized storage for NULL values. Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. Thus, they reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

Sparse columns should be used when the space saved is at least 20 percent to 40 percent. Sparse columns can be used with column sets (column sets are defined by using the CREATE TABLE or ALTER TABLE statements) and filtered indexes.

Example

CREATE TABLE DocumentStore

(in_doc_id int PRIMARY KEY,

vc_title varchar(200) NOT NULL,

vc_production_specification varchar(20) SPARSE NULL,

in_production_location smallint SPARSE NULL,

vc_marketing_survey_group varchar(20) SPARSE NULL);

GO

Characteristics of Sparse Columns

• The Sparse keyword is used by the SQL Server Database Engine to optimize the storage of values in that column. When the column value is NULL for any row, the values require no storage.
• A table having sparse columns has the same catalog views as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.
• Since sparse columns are a property of the storage layer, a SELECT…INTO statement does not copy over the sparse column property into a new table.