SQL Server: Amazing Sparse Columns an optimized storage for NULL values

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

This is one of the important article because NULL value is occupying space in the SQL Server.

You can visit, Recently shared one article on NOT NULL vs NULL.

NULL is an Unknown value and it occupies space in SQL Server based on the column data type.

Fixed length Character:
CHAR(3): NULL value occupies 3 bytes.

Integer data type:
int: NULL value occupies 4 bytes.

Variable length data type:
VARCHAR(10): NULL value occupies 2 bytes.

The Sparse Columns:

It is just like any other column, but it reduces the space requirements for the NULL values.

As per the Microsoft’s official document, it saves the space at least up to 20% to 40%.

We can specify SPARSE keyword in the column definition for making SPARSE Column.

Create a table with SPARSE column: