Notes on SQL

Random articles from a puzzled DBA

A column set is an untyped XML column that can be used to update and select all sparse columns defined in the associated table. This XML is not physically stored in the table – it is in effect a calculated column that can be used to update.
Microsoft recommend its use for tables that contain a large number of sparse columns, although it is not without its overheads.
It has some interesting effects upon updates and selects, for the table that uses it.

Test Environment

A default database is created, with a basic test table within:

Listing 1: Create Initial Test Environment

CREATE Database Demo1;

USE Demo1;

	ID               INT IDENTITY(1,1)  NOT NULL,
	ProductName      VARCHAR(50)        SPARSE,
	[Description]    VARCHAR(100)       SPARSE,
	InStock          BIT                SPARSE,
	DateCreated      Date               SPARSE,

Apart from the ‘ID’ column, all other columns have the SPARSE property.
The column ‘CSet’ is the column set – an XML column that has ‘FOR ALL_SPARSE_COLUMNS’ specified. At the time of writing this article ‘ALL_SPARSE_COLUMNS’ is the only option available for a column set definition.

Inserting data

Data can be inserted into this table by inserting the individual column data, or by inserting an XML value via the ‘CSet’ column set:

Listing 2: Inserting Rows

INSERT INTO Demo1([ProductName], [Description], [InStock], [DateCreated])
VALUES('Test Product1','Test Product for no particular reason',0,'01 Feb 2018');

VALUES('<ProductName>Test Product2</ProductName><Description>Another Test Product</Description><InStock>0</InStock><DateCreated>2018-02-25</DateCreated>');

Figure 1: Result from Listing 2

Column set data inserted

Notice in the example above, the column ‘CSet’ is always populated – even if the data was not inserted via that column. This XML value will contain the values of all non-null sparse columns.

What you cannot do, is a combination of both – you can’t insert directly into one sparse column and into the other sparse columns via the column set:

Listing 3: Incorrect Insert

INSERT INTO Demo1(ProductName, CSet)
VALUES('Test Product 3','<InStock>0</InStock><DateCreated>2018-06-10</DateCreated>');

This results in the error message “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.”

And if you don’t include a column, it will of course default to NULL:
Listing 4: Omitting a column (Description)

VALUES('<ProductName>Test Product3</ProductName><InStock>0</InStock><DateCreated>2018-06-10</DateCreated>');

Figure 2: Result from Listing 4

‘Description’ is NULL


The behavior of ‘SELECT *’ changes, where a table has a column set. It will not return all columns but just the non-sparse columns and the column set value. This may be important if you expect all columns to be returned.
This can also been seen when using SQL Server Management Studio (SSMS) to return the top rows, from the right-click option against the table.

Figure 3: SELECT *

SELECT * result

UPDATE via column set

A column set can be used to update a row but every sparse column that is required to have a non-null value must be specified. If a column has an existing value and it needs to keep that after the update, it must be included in the column set.

Listing 5: Updating via column set:

SELECT [ID], [ProductName], [Description], [InStock], [DateCreated], [CSet]
FROM [dbo].[Demo1];

  UPDATE dbo.Demo1
  SET CSet = '<Description>Updated Description for Test Product3</Description>'
  WHERE ProductName = 'Test Product3';

SELECT [ID], [ProductName], [Description], [InStock], [DateCreated], [CSet]
FROM [dbo].[Demo1];

Figure 4: Result from Listing 5

Column data removed that was not in column set

Adding a column set to an existing table

If you need to add a column set to an existing table, the table must be dropped and re-created with the column set. It is not possible to add a column set to an existing table.


Use Column Sets
Use Sparse Columns

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: