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

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “SQL Server What is a Sparse Column?”

Leave a Reply

Your email address will not be published. Required fields are marked *