Sparse column in SQL Server

A sparse column is a special column type in SQL Server designed to optimize storage for NULL values. Unlike a normal column, a sparse column stores nothing at all when the value is NULL.

This feature was introduced in SQL Server 2008 and is primarily meant for wide tables and optional attributes.

A normal column consumes fixed bytes, even when it’s NULL. A sparse column consumes zero bytes when NULL, but when it has a value, SQL Server adds four bytes of overhead to track it. So, the rule is simple If more than 40 to 50 percent of the values are NULL, sparse columns save space. If not, they can actually waste space.

Sparse columns optimize storage, not performance.

Sparse columns come with strict rules:

Ø  They must allow NULL

Ø  They can’t be PRIMARY KEY

Ø  They can’t be IDENTITY

Ø  They can’t have DEFAULT values

Ø  They can’t be NOT NULL

Indexes are allowed, but they grow larger.

Let’s see the example

Here we are creating two table one is sample table and other one is Sparse columns table and see the storage of both.

CREATE TABLE Sparse_Demo_Normal (     ID                                                               INT IDENTITY(1,1) not null PRIMARY KEY,               columnone                                   TINYINT null,               columntwo                                   SMALLINT null ,               columnthree                                INT        null,               columnfour                                   BIGINT null,               columnfive                                    DECIMAL(30,10) null ,               columnsix                                      MONEY null,               columnseven                               SMALLMONEY null,               columneight                                 char(10) null,               columnnine                                  varchar(max)null,               columnten                                    nvarchar(max)null,               columneleven               DATE null,               columntwelve               TIME null,               columnthirteen                           DATETIME null,               columnfourteen                          SMALLDATETIME null,               columnfifteen                DATETIME2 null,               columnxixteen              DATETIMEOFFSET null,               columnseventeen                       UNIQUEIDENTIFIER null,               columneighteen                          NVARCHAR(MAX) null,               columnninteen                            BIT null,               columntwenty               VARBINARY(MAX) null               ); CREATE TABLE Sparse_Demo ( ID                                                                    INT IDENTITY(1,1) not null PRIMARY KEY,               columnone                                   TINYINT SPARSE null,               columntwo                                   SMALLINT SPARSE null              ,               columnthree                                INT        SPARSE null,               columnfour                                   BIGINT SPARSE null,               columnfive                                    DECIMAL(30,10) SPARSE null ,               columnsix                                      MONEY SPARSE null,               columnseven                               SMALLMONEY SPARSE null,               columneight                                 char(10) SPARSE null,               columnnine                                  varchar(max)SPARSE null,               columnten                                    nvarchar(max)SPARSE null,               columneleven               DATE SPARSE null,               columntwelve               TIME SPARSE null,               columnthirteen                           DATETIME SPARSE null,               columnfourteen                          SMALLDATETIME SPARSE null,               columnfifteen                DATETIME2 SPARSE null,               columnxixteen              DATETIMEOFFSET SPARSE null,               columnseventeen                       UNIQUEIDENTIFIER SPARSE null,               columneighteen                          NVARCHAR(MAX) SPARSE null,               columnninteen                            BIT SPARSE null,               columntwenty               VARBINARY(MAX) SPARSE null    );   --inserting 100k records into this table declare @i int=1; while (@i<=100000) begin insert into Sparse_Demo_Normal (columnone,columntwo,columnthree,columnfour,columnfive,columnsix,columnseven,columneight,columnnine                            ,columnten,columneleven,columntwelve,columnthirteen,columnfourteen,columnfifteen,columnxixteen,columnseventeen              ,columneighteen,columnninteen,columntwenty        ) values (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, null,null,null,null,null) insert into Sparse_Demo(columnone,columntwo,columnthree,columnfour,columnfive,columnsix,columnseven,columneight,columnnine                             ,columnten,columneleven,columntwelve,columnthirteen,columnfourteen,columnfifteen,columnxixteen,columnseventeen              ,columneighteen,columnninteen,columntwenty        ) values (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, null,null,null,null,null); set @i=@i+1; end;   go

Both tables created and inserted 100K records into the both data.

See the data.

Now see the space in both tables.

EXEC sp_spaceused 'Sparse_Demo_Normal'; EXEC sp_spaceused 'Sparse_Demo';

Sparse column took less storage space.

Limitations of Sparse Columns

Sparse column is good for the null columns storge it has below limitations

Ø  Storage Penalty for Non-NULL Values

o   Caused by off-row storage + sparse vector metadata

o   ~4 extra bytes per non-NULL value

Ø  Sparse columns do NOT improve query speed 

Ø  Must be NULLABLE
Ø  Default values do not work
Ø  Sparse columns do not support all data types

o   TEXT, NTEXT, IMAGE

o   TIMESTAMP

o   Some CLR UDTs

Ø  Sparse columns cannot be used in

o   PRIMARY KEY 

o   FOREIGN KEY  

o   IDENTITY          

o   ROWGUIDCOL

o   Computed Column     

o   Indexed View  

o   Partitioning Key