Thursday, August 12, 2021

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.

1 comment:

SQL Script to list out name of tables and its records in the database

SET NOCOUNT ON  DBCC UPDATEUSAGE(0)  -- DB size. EXEC sp_spaceused -- Table row counts and sizes. CREATE TABLE #temptable  (      [name] NVA...