SPARSE Column in SQL Server

Jignesh Trivedi
2y
76.1k
0
4

Article

Introduction

In this article, we will learn about SPARSE Column in SQL Server. The SPARSE column is a good feature of SQL Server. It helps us to reduce the space requirements for null values. Using a SPARSE column, we may save up to 20 to 40 percent of space.

SPARSE Column in SQL Server

A SPARSE column is a common column with optimized storage for NULL values. It also reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server. It does not occupy any space in the database. Using a SPARSE column, we may save up to 20 to 40 percent of the area. We can define a column as a SPARSE column using the CREATE TABLE or ALTER TABLE statements.

Syntax

CREATE TABLE TableName
(
      .....
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
      .....
)

We may also add/change a column from the graphical view.

Example

In this example, I have created two tables with the same number of columns and the same data type, but one table's columns are created as a SPARSE column. Each table contains 500+ rows.

CREATE TABLE TableName
(
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
)

CREATE TABLE TableName1
(
      Col1 INT ,
      Col2 VARCHAR(100) ,
      Col3 DateTime
)

Using the sp_spaceused stored procedure, we can determine the space occupied by the table data.

sp_spaceused 'TableName'
GO
sp_spaceused 'TableName1'

Advantages of a SPARSE column

A SPARSE column saves database space when there are zero or null values.
INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
We can get more benefits from Filtered indexes on a SPARSE column.
We can use SPARSE columns with change tracking and change data capture.

Limitations of a SPARSE column

A SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
It cannot have a default value and bounded-to rule.
A SPARSE column cannot be part of a clustered index or a unique primary key index and partition key of a clustered index or heap.
Merge replication does not support SPARSE columns.
The SPARSE property of a column is not preserved when the table is copied.