Introduces the concept of the sparse column

SQL Server 2008 introduces the concept of the sparse column, which is a type of column that optimizes storage for null values. When a column contains a substantial number of null values, defining the column as sparse can save a significant amount of disk space. In fact, a null value in a sparse column takes up no space at all.

There are trade-offs, however, in using sparse columns because more space is needed for non-null values. In a column configured as sparse, the non-null value requires an additional 4 bytes of storage. For example, a DATETIME value in a non-sparse column requires 8 bytes of storage. In a sparse column, however, that value requires 12 bytes. For this reason, Microsoft recommends that you use sparse columns only when the space saved is at least 20 to 40%.

If you decide to implement sparse columns, keep in mind that there are a number of restrictions. For example, the column must be nullable and cannot be configured with the ROWGUIDCOL or IDENTITY properties, cannot include a default, and cannot be bound to a rule. In addition, you cannot define a column as sparse if it is configured with certain data types, such as TEXT, IMAGE, or TIMESTAMP.

EXAMPLE

CREATE TABLE dbo.DocumentStore

            (DocID int PRIMARY KEY,

             Title varchar(200) NOT NULL,

             ProductionSpecification varchar(20) SPARSE NULL,

             ProductionLocation smallint SPARSE NULL,

             MarketingSurveyGroup varchar(20) SPARSE NULL ) ;

 

You can find a complete list of restrictions on sparse columns via Microsoft's

http://msdn.microsoft.com/en-us/library/cc280604.aspx

Posted By: MR. JOYDEEP DAS