Sparse Columns

SQL Server 2008 provides a new space-saving storage option referred to as sparse columns. Sparse columns can provide optimized and efficient storage for columns that contain predominately NULL values. The NULL values require no storage space, but these space savings come at a cost of increased space for storing non-NULL values (an additional 2–4 bytes of space is needed for non-NULL values). For this reason, Microsoft recommends using sparse columns only when the space saved is at least 20% to 40%. However, the consensus rule of thumb that is emerging from experience with sparse columns is that it is best to use them only when more than 90% of the values are NULL.

There are a number of restrictions and limitations regarding the ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.