SPARSE Column in SQL Server

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.

Conclusion

In this article, we learned about the SPARSE column in SQL Server.