Have you ever come across the concept of sparse columns in SQL Server? Sparse columns are a powerful feature that allows you to optimize storage for tables with many NULL values. In addition to their storage benefits, sparse columns also provide the ability to write to column sets directly. In this blog post, we will take a tour of how this can be achieved.

Let’s start by creating a table with sparse columns and a column set:

CREATE TABLE colSet (
	c1 INT SPARSE,
	c2 CHAR (20),
	c3 DATETIME sparse,
	cs XML column_set FOR ALL_SPARSE_COLUMNS)
	

When inserting data into individual columns of a column set, it is important to use an explicit column list. Otherwise, you will encounter an error:

INSERT INTO colset VALUES (3, 'abc', GETDATE())

The above statement will result in the following error:

Msg 213, Level 16, State 1, Line 9
	Column name or number of supplied values does not match table definition.

To insert data into the column set itself, you need to specify XML syntax to reference the columns:

INSERT colSet (c2, cs)
	VALUES (3, '<c1>7</c1><c3>01/01/15</c3>');
	INSERT colSet (c2, cs)
	VALUES (4, '<c1>7</c1><c3>Feb 10 2015 7:05PM</c3>');

However, be cautious not to insert into a column set both directly via an explicit column name and via a column set XML syntax. This will result in an error:

CREATE TABLE colSet2
	(c1 INT,
	c2 CHAR (20) SPARSE,
	c3 DATETIME sparse,
	cset XML column_set FOR ALL_SPARSE_COLUMNS)
	INSERT colSet2 (c2, cset)
	VALUES ('SQLAuthority', '<c2>SQLAuthority</c2><c3>01/01/08</c3>')

The above statement will result in the following error:

Msg 360, Level 16, State 1, Line 7
	The target column list of an INSERT, UPDATE, or MERGE statement cannot contain both a sparse column and the column set that contains the sparse column.
	Rewrite the statement to include either the sparse column or the column set, but not both.

It is important to note that sparse columns can be a valuable tool in certain scenarios where you have tables with many NULL values. They can help optimize storage and improve query performance. If you are using sparse columns in your environments, I would love to hear about your use cases and experiences. Feel free to drop a comment and let’s learn together!

That’s all for now. Stay tuned for more SQL Server tips and tricks!