Sparse Columns in SQL Server

Sparse Columns in SQL Server

Sparse column is introduced in SQL Server 2008, which is designed to store null values. If you store any null value in sparse column it doesn’t occupy any space on the database. If you store any non null value in sparse column it takes 4 bytes extra space. For example if you store bigint in database usually it requires 8 bytes if you store this value in sparse column it will occupy 12 bytes.

CREATE TABLE Students_Sparse (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30) SPARSE)

CREATE TABLE Students (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30))

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

TRUNCATE TABLE Students_Sparse

TRUNCATE TABLE Students

Below example shows if you leave the sparse column by entering null values the column doesn’t occupy any space and there will be a huge change in space

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP',null)

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students