Wednesday, March 13, 2013

SPARSE Columns

A column can be defined as SPARSE column in SQL Server Version 2008 and above. This would give advantage in terms of memory occupied  , if the value stored is NULL.The space saved may be 20% to 40%(msdn).

The below data types cannot be marked as SPARSE:
     Text,NText,geography,geometry,timestamp,image and user defined data types.

Below are the example:

We are trying the compare benefits of using SPARSE on table with CHAR data type columns and table with  VARCHAR data type columns.

Comparison between the tables with and without SPARSE columns( as in the above ex.) might convey that the SPARSE declaration of a column would be beneficial if the column datatype is of fixed length storage type. Also, the VARCHAR type comparison gives that the space saved is negligible.

We have additional overhead in fetching the NON-NULL values of SPARSE columns.

So,analyse and based on the amount of benefit  ,proceed with SPARSE columns in a table