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.
Step 2: Now set the Sparse Property.
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!!