SQL Server has supported the concept of Sparse Columns since 2008 R2. The purpose of the Sparse columns is to optimize storage and performance in those instances where you have a table containing a lot of null data. In my experience, this is often due to a schema that is not normalized. While normalization is generally the best policy, there are times when a de-normalized schema may be desirable. When that occurs, Sparse Columns may provide benefits to your database.
Sparse Columns optimize the storage of data for null values. The amount of space saved when using a Sparse column definition depends on the data type being stored. Since additional space is required for non-null values when stored in a Sparse column, it is important to have a large number of records with null values to gain the best optimization.
Any column may be defined to allow null values. A Sparse column must be defined as allowing null. Additionally, it is defined as a Sparse column. This tells SQL Server that not only are null values allowed, but, null values are more common than values containing data.
A table containing a lot of Sparse columns may be optimized even further by using Column Sets. Column sets combine all of the Sparse columns containing non-null values into a single xml fragment for a single row. In essence, this allows you to have a dynamic data structure in the form of an XML fragment, each capable of having different nodes from those of other rows. It’s like having your table schema change from one row to the next, based on the non-null values.
This makes it easier to operate on the columns as a set, rather than operating on each column individually. Both techniques work, so you can choose the method that is easiest for you to implement at the time.
According to TechNet, Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns. For further guidance, check out the TechNet documentation for Using Column Sets.
Cheers,
Ben