Saturday, March 07, 2009

SPARSE column property in SQL Server 2008

I'm reading: SPARSE column property in SQL Server 2008Tweet this !
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

No comments:

Related Posts with Thumbnails