Using Column Sets for Sparsely Populated Columns

SQL Server has a little-known feature called Column Sets that is very useful when dealing with large numbers of sparse columns. Sparse columns are useful when you need a table with many columns where the rows are sparsely populated with data. Sparse columns can be a great alternative to using Entity-Attribute-Value design-patterns, and Key-Value stores, where the attributes or keys are known in advance.

thomas cole not column sets

    What a nice view! Almost as nice as looking at column sets!

Imagine you want to track values for a variety of types of exercise. You capture the type of exercise, the date, and a variety of other details that change with each type of exercise. So, if you’re running you might want to know how far you ran, the route, and the best segment time. If you’re using the treadmill, you might want to know the “program” you used, and the maximum speed you reached. When skiing, you want to know what difficulty level the ski run was, as well as where you skied. You could set this up in a single table, using sparse columns, like this:

Inserting three separate exercises can be accomplished two ways. First, the traditional method where we insert discrete values into each column:

Using SELECT * against a table with a COLUMN_SET column is a little different. The column set column combines the sparse columns into a single XML column, with elements for each non-null sparse column. It looks like this:

Column Sets Output #1

(click to enlarge)

Notice many of the columns are not returned by SELECT *, whereas normally, every column is part of the result set.

Since column set columns are directly updatable, you can insert values using this method:

As you can see from this query where we’re discretely choosing all columns in the table, both methods end up with data inserted the same way:

Column Sets Output #2

(Click to enlarge)

This post shows how to make use of the underappreciated COLUMN SETS feature in SQL Server. The next post in this series shows how to set up check constraints with column sets.

Check Microsoft’s documentation for column sets here.

This post is part of our series on T-SQL.