SQL SERVER – Inserting into Sparse Column Sets and Errors Associated With It

I have written tons of blogs in the past around using sparse columns. The best part and lesser known part of sparse columns is that we can write to the column sets directly too. In this blog let me take a tour of how this can be achieved.

When sparse column values are inserted or updated by using the XML column set, the values that are inserted into the underlying sparse columns are implicitly converted from the xml data type. When inserting data explicitly into individual columns of a column set, you MUST use an explicit column list.

-- Create 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)
--Try to insert a row without specifying a column list
INSERT INTO colset VALUES (3, 'abc', GETDATE())

As mentioned before, we will encounter the following error because the column names are not mentioned explicitly.

Msg 213, Level 16, State 1, Line 9

Column name or number of supplied values does not match table definition.

The correct version would be as shown below:

--Try to insert a row by specifying a column list - successful
INSERT INTO colset (c1,c2, c3) VALUES (3, 'abc', GETDATE())

Step 2, let us try to insert data into the column set itself, you have to specify XML syntax to reference the columns.

-- Insert two rows by specifying a column set- successful. Note we are specifying C2 and CS – the column set. use the table created above
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>');

Be careful not to insert into a column set both directly via an explicit column name, and via a column set XML syntax.

--Create table
CREATE TABLE colSet2
(c1 INT,
c2 CHAR (20) SPARSE,
c3 DATETIME sparse,
cset XML column_set FOR ALL_SPARSE_COLUMNS)
--Attempt to insert into table both via column name and column set
--This will fail because C2 is reference twice: explicitly on a column list and in the column set
INSERT colSet2 (c2, cset)
VALUES ('SQLAuthority', '<c2>SQLAuthority</c2><c3>01/01/08</c3>')

We will be encountering the following error in this case.

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.

Let us go ahead and clean up the tables we used.

-- Clean up
DROP TABLE colSet2
DROP TABLE colSet

Though I have written quite a few blogs on sparse columns. Do let me know if you are using any of these in your environments? What are those scenarios? Drop a comment and let us learn together.

Reference : Pinal Dave (https://blog.sqlauthority.com)