SQL Server What is a Sparse Column?

Hello Folks,

You would have heard about Sparse Column.It has been introduced earlier with the arrival of SQL Server 2008.

Well I have made some keynotes about SQL Server What is a Sparse Column?, so please go through it carefully:

Sparse columns allow for the optimized storage of null columns.
It reduces the space requirements for null values at the cost of more overhead to retrieve non-null values.
Sparse columns do not take any space in database at all.
As we know that the maximum column allowed per table in SQL Server is 1024, and so the sparse column does not count to this limit of 1024.
Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index.
Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server.
It will become more clear to you if you see the following example:

Well, there are two ways by which you can be able to apply the Sparse Column property;

1. Using the SQL Server Management Studio.

Step 1: Right-click the desired table and choose the design.

1_SQL_Server_What_is_Sparse_Column

Step 2: Now set the Sparse Property.

2_SQL_Server_What_is_Sparse_Column

2. Using the T-SQL.

USE TEST
CREATE TABLE dbo.abc
(
oldvalue VARCHAR(50) SPARSE NULL,
newvalue VARCHAR(50) NULL
) ON [PRIMARY]
GO

Side-Effects of Using Sparse Columns:

If a sparse column has a data in it, then it will take 4 more bytes than a normal column. Therefore, the storage requirements may go up instead of down.
The data type like text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse.
The data compression and the merge replication won’t able to work with it.

Well, this was a bit about Sparse Columns in SQL Server.

Hope you enjoyed read the stuff!!

If you liked this post, do comments on this!!

Regards

Piyush Bajaj