Sparse columns, An oblivious for any data/dimensional modeler !
Have you anytime been knocked on the head by the client on how can you as a data modeler taken actions to reduce space usage in the server?
One of the most oblivious to any data/dimensional modeler is the efficient way of minimizing the space used by the database on the disk. So how can we achieve that? Below is one of the trick which can be used. Sparse columns in SQL Server. It is an fun-tas-tic piece of thing which can save you lot of space of your database.
1. What is a sparse column?
A sparse column in a table is the one which contains a lot of NULL values. It may be required as per design.
2. What are the possible cases of sparse column?
In the design of any table there will be columns which can take NULL values and most of the rows in the column will be NULL due to nature of transaction. For ex: Spouse Name, If 50% are more are not married like me
then you will have up to or more than 50% rows being null.
3.How to we efficiently handle the sparse column in terms of minimizing the space?
Use SPARSE keyword when you are defining a table to optimize the columns which contain high % of NULL Values.
In this blog, I have identified two main areas where I could see the optimization of space.
1. Data space ( With and without SPARSE keyword )
2. Index space ( Filtered and Normal Index on top of sparse column defined using SPARSE keyword )
3. Combining the both ( 1 and 2 )
The Sparse Keyword optimization algorithm works differently for different data types.
Combining the Data + Index space usage
So, if you can combine the filtered index on top of a sparse column you can have a space saving of upto 70% * in certain cases.
But there are certain caveats like the SPARSE keyword cannot be used on certain data types and memory restrictions for updating the SPARSE columns.
For more information on the above points refer the MSDN blog :https://msdn.microsoft.com/en-us/library/cc280604.aspx
That’s all for now, please share your feedbacks and comments.