SPARSE column property in SQL Server 2008

Designed to optimize storage space for columns with a large percentage of NULLs, the option to designate a column as sparse is new in SQL Server 2008. To designate a column as SPARSE, the column must allow NULLs. When a NULL is stored in a column designated as SPARSE, no storage space is consumed. However, non-NULL values require 4 bytes of storage space in addition to the normal space consumed by the data type. Unless you have a high enough percentage of rows containing a NULL to offset the increased storage required for non-NULL values, you should not designate a column as SPARSE.

You cannot apply the SPARSE property to:

• Columns with the ROWGUIDCOL or IDENTITY property
• TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, or user-defined data types
• A VARBINARY(MAX) with the FILESTREAM property
• A computed column of a column with a rule or default bound to it
• Columns that are part of either a clustered index or a primary key
• A column within an ALTER TABLE statement

More details on space saving by data-type and restrictions for using SPARSE columns can be found on BOL.

Reference: Microsoft Press Books