SQL SERVER – Updating Data into ColumnStore Index with SQL Server 2012

Off late I have been writing tons of content working with SQL Server 2016. Some of the cool capabilities was to introduce the ability to update a ColumnStore index. This was also covered in the SQL SERVER 2016: Updating Non-Clustered ColumnStore Index Enhancement earlier. One of the readers asked me an interesting question about being inside SQL Server 2012 and they wanted to write / update their data on the ColumnStore enabled system and what was the process for them. The email started saying they were getting the following error.

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

The error message recommends a ‘workaround’, but rebuilding the column store index for updates may be prohibitively expensive. This is typically what most of you will get by working with SQL Server 2012 and ColumnStore Indexes. I was asked for possible solutions / processes so that we can do this in an efficient manner. I wrote back with the following recommendation.

For the DW and BI scenarios that column store indexes are targeting, there is a much better solution: use table partitioning. With SQL Server 2012, the limit of maximum 1000 partitions per table has been increased to 15000 partitions; and with this new limit, one can configure the ETL process to update every day into a new partition and still retain many years of data. The ETL process can upload the daily data into a staging table, create a columnstore index on the staging table, then use the fast ALTER TABLE … SWITCH operation to ‘switch in’ the new data.

Other methods to update a columnstore index include:

1. Disable or drop the columnstore index. You can then update the data in the table. If you disable the columnstore index, you can rebuild the columnstore index when you finish updating the data, for example:

ALTER INDEX myColumnStoreIndex ON myDWtable DISABLE;

— update the data —

ALTER INDEX myColumnStoreIndex ON myDWtable REBUILD;

Now the column store index is ready to use again.

2. Switch a partition from the table with the column store index into an empty staging table. If there is a column store index on the staging table, disable the column store index. Perform any updates. Build (or rebuild) the column store index. Switch the staging table back into the (now empty) partition of the main table.

These are classic scenarios and well documented by a number of folks. I would like to understand if you are using SQL Server 2012 version with ColumnStore? Are you handling in one of the above mentioned processes? I would love to hear if you are using any other techniques. Please use the comments section below to explain the same.

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