What are Sparse columns and when do we need it?

Sparse Columns:

Sparse columns are very much useful while storing 0(Zero) or NULL values. Unlike regular columns, Sparse columns does not consume space in database. This was introduced in SQL Server 2008. As we all know that we can create 1024 columns in SQL server. But unbelievable fact in Sparse columns is we can create 1,00,000 sparse columns on a table.

              As coin has two sides, this too have one advantage as well as one disadvantage. Sparse columns are mainly useful only when we have more 0’s or NULLs in the column. It does not take database space if the value is 0 or NULL. Otherwise it will consume 4 more bytes space consumed by regular columns. 

Ex:

———————————————————————–
— Creating a table without Sparse column
———————————————————————–
CREATE TABLE NoSparsed(
Emp_ID INT PRIMARY KEY,
Ename VARCHAR(100),
Salary DECIMAL(10,0),
Address_Line_1 VARCHAR(100),
Address_Line_2 VARCHAR(100),
Address_Line_3 VARCHAR(100),
Address_Line_4 VARCHAR(100),
Address_Line_5 VARCHAR(100)
)
GO
———————————————————————–
— Creating a table with Sparse columns
———————————————————————–
CREATE TABLE Sparse(
Emp_ID INT PRIMARY KEY,
Ename VARCHAR(100),
Salary DECIMAL(10,0),
Address_Line_1 VARCHAR(100),
Address_Line_2 VARCHAR(100),
Address_Line_3 VARCHAR(100),
Address_Line_4 VARCHAR(100) SPARSE,
Address_Line_5 VARCHAR(100) SPARSE
)
GO
———————————————————————–

Happy Reading :)

Reference: MSDN

Leave a Reply

Your email address will not be published. Required fields are marked *