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)
3 Comments. Leave new
Thank you for your article !
(SharePoint is full of sparse columns, this is why I came here to understand it ;) )
I didn’t know that Fabien. Thanks for sharing.
I get the same error, “Column name or number of supplied values does not match table definition.” when I try to insert into my table using a select statement. Also my table to insert into has over 7000 columns. It’s for a medical report so I have to use sparse columns. How can I resolve this? Thanks.