SQL SERVER – Inserting into ColumnSet with SPARSE Columns

There are a number of blogs around using SPARSE columns here. Here are few of them for reference and can be used as a great learning experience.

SQL SERVER – 2008 – Introduction to SPARSE Columns

SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

Though such capabilities exist inside the product, I hardly see people using this feature effectively. Recently, one friend from a product based company called me to check if I had written on this topic. I explained that there are tons of documentation and it is great to see them use it. He was saying, they had a table which had close to 1000’s of column as they are using it like a generic table and wanted to know if there is an easy way to update or insert into this SPARSE table directly from ColumnSet?

The short answer is, YES. It is quite possible to insert using the ColumnSet column directly. I sent him an example and thought it would be of great use for folks here too.

Solarwinds

Let us create our table for the experiment:

USE tempdb;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DocStore_With_ColumnSet')
BEGIN
DROP TABLE
DocStore_With_ColumnSet
END
-- note the use of the SPARSE keyword and the XML column_set
CREATE TABLE DocStore_With_ColumnSet
(DocID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
ProductionSpecification VARCHAR(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup VARCHAR(20) SPARSE NULL,
MarketingProgramID INT SPARSE NULL,
Special_XML_Column XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
GO

We can insert into different values columns as shown below:

INSERT DocStore_With_ColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Specs 1', 'ABC321', 27)
GO
INSERT DocStore_With_ColumnSet (DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 1234', 'Female 25 - 35')
GO
-- Note that the Special_XML_Column column is correctly populated based on the columns that are populated
SELECT * FROM DocStore_With_ColumnSet
GO

Can we add to the ColumnSet directly? Here is what we can do:

-- you can add data directly by specifying the xml
INSERT DocStore_With_ColumnSet (DocID, Title, Special_XML_Column)
VALUES (3, 'Specification 2', '<ProductionSpecification>AXZ7R242</ProductionSpecification><ProductionLocation>18</ProductionLocation>')
GO

We can see the success of the insert statement. Let us next try to check if the values are inserted into our ColumnSet:

SELECT * FROM DocStore_With_ColumnSet
GO

If this was not enough, we can also use this technique to update the columnset values too. A typical example for this is shown below:

-- now let us update some of the data
UPDATE DocStore_With_ColumnSet
SET Special_XML_Column = '<ProductionSpecification>ZZ456Z</ProductionSpecification><ProductionLocation>18</ProductionLocation>'
WHERE DocID = 3;
GO

You can see again by querying that the values have been inserted successfully. This was a simple example to how we can use columnsets to insert data into a sparse table rather can doing a qualification of each columns. Let us next try to clean this table:

-- Cleanup
DROP TABLE DocStore_With_ColumnSet
GO

As I conclude, would like to know how many of you have used SPARSE columns in your application and what were some of the scenarios you were able to lighten up using this feature?

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

Solarwinds
Previous Post
SQL SERVER – InMemory Table Creation Error Message: Msg 41337
Next Post
Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?

Related Posts

2 Comments. Leave new

  • I think it would be useful for auditing or taking logs.

    I am working on a project that our system need to communicate with other parties.
    Now I have a table to log the input parameters and the output columns in our store procedures so that I can know what they have been done in my database and check if I am returning the correct value to them.

    But I did not note that SQL Server has such feature, so I just create a table to store those things, every input parameter and output columns are stored as single column. Some people suggest me to combine those fields and stored in a single varchar(max) column. But it required me to convert those fields to strings first.

    Since more store procedures are added for the new features, more input, output columns I needed to added to the table. And the input, output columns are quite different in every store procedure. So every row of logging data are storing null value in those not related columns.

    Using sparse columns can save a lot of space in this case. I would like to try to change to use sparse columns in my testing environment.

    Reply
  • dtaye@yahoo.com
    July 10, 2017 9:26 pm

    I know this blog a little old, but we have more than 4000 column and we defined the table using column set, however the problem is inserting more than 2048 column at a time using SSIS. here is the error

    [OLE DB Destination [11551]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The statement has been terminated.”.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The number of columns in the column set exceeds 2048. Reduce the number of columns that are referenced in the column set.”.

    Reply

Leave a Reply

Menu