SPARSE Column in SQL Server

A SPARSE column is a special type of column that has optimised 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 store any space in the database. Using a SPARSE column we may save up to 40% of space. We can define a column as a keyboard SPARSE column using the CREATE TABLE or ALTER TABLE commands.

Syntax

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

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



Example

In this example I have create two tables with the same number of columns and the same data type but one table's columns are created as a SPARSE column. both tables contains 717 rows individuals.

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'

RESULT

Advantages of a SPARSE column

• A SPARSE column saves database space when there is zero or null values in the database.
• INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
• We can get more benefit of 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

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 40 % of space.