SQL Server 2012 introduced the powerful ColumnStore index feature, which greatly enhances the performance of data warehousing and business intelligence scenarios. However, one limitation of SQL Server 2012 is that it does not allow direct updates on tables with a ColumnStore index. This can be frustrating for users who need to update their data on a ColumnStore enabled system.
When attempting to update data in a table with a ColumnStore index, you may encounter the following error message: “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 suggests a workaround, which involves disabling the ColumnStore index before performing the update and then rebuilding it afterwards. However, this approach can be time-consuming and resource-intensive.
Fortunately, there are more efficient solutions available. One recommended solution is to utilize table partitioning. With SQL Server 2012, the maximum limit of partitions per table has been increased to 15,000. This allows you to configure your ETL (Extract, Transform, Load) process to update data into a new partition on a daily basis while retaining historical data for many years.
The ETL process can upload the daily data into a staging table, create a ColumnStore index on the staging table, and then use the fast ALTER TABLE … SWITCH operation to “switch in” the new data. This approach eliminates the need to disable and rebuild the ColumnStore index for each update.
Other methods to update a ColumnStore index include:
- Disabling or dropping the ColumnStore index, updating the data, and then rebuilding the index.
- Switching a partition from the table with the ColumnStore index into an empty staging table, disabling the ColumnStore index on the staging table, performing updates, and then building or rebuilding the ColumnStore index.
These methods have been widely used and documented by SQL Server professionals. If you are using SQL Server 2012 with ColumnStore, we would like to hear about your experiences. Are you using one of the above-mentioned processes, or do you have any other techniques? Please share your thoughts and insights in the comments section below.