Sparse Column

With the introduction of the new Sparse Column feature in SQL Server 2008, it is now possible to declare a column as Sparse and any time a NULL value is entered in the column it will not use any space. One of the tricks here is to figure out when to determine when a column should be defined as Sparse or not.

So why not just declare all columns as Sparse Columns?

Sparse Columns require four extra bytes of storage for each non NULL fixed-length data type value in the table and zero bytes to store a NULL value; therefore it is very important to have the correct threshold per data type or you will end up using more space instead of gaining it. The fewer bytes a data type uses, the higher the percentage of NULL values are required to save space.

CREATE TABLE CustomerInfo

(CustomerID INT PRIMARY KEY,

Address_Line1 VARCHAR(100) NOT NULL,

Address_Line2 VARCHAR(100) NOT NULL,

Address_Line3 VARCHAR(100) NOT NULL,

Address_Line4 VARCHAR(100) NULL,

Address_Line5 VARCHAR(100) NULL,

Address_Line6 VARCHAR(100) SPARSE NULL,

Address_Line7 VARCHAR(100) SPARSE NULL,

Address_Line8 VARCHAR(100) SPARSE NULL,

)