SQL Server Portal

Statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the statement, then rebuilding the columnstore index after S_MSG is complete is one of the new error messages shipped with SQL Server 2012 and related to columnstore index.

Let’s discuss this in detail:
Message Number: 35330

Severity : 15

Error Message:  %S_MSG statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the %S_MSG statement, then rebuilding the columnstore index after %S_MSG is complete.

Error Generation:
Let’s generate this error step by step.

Step 1:
Create a table to demonstrate this error.

Use AdventureWorks2012
Go
Create Table tbl_Product_Master
(
[Product ID] int,
[Product Name] nvarchar(100),
[Model] int
)
Go

Step 2:
Insert a record in the table.

Use AdventureWorks2012
Go
insert into tbl_Product_Master values (1,'Car',2011)

Step 3:
Browse the table to check that the record has been updated properly.

Use AdventureWorks2012
Go
Select * from tbl_Product_Master

Step 4:
Create a columnstore index on this table.

CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product Name], [Model],[Product ID])

Step 5:
Try to execute insert, delete and update statements to perform transactions in the table.

insert into tbl_Product_Master values (2,'Car',2012)

Ooopps…… I am unable to execute it.

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.

delete from tbl_Product_Master where [Product ID]=1

Ooopps…… I am still unable to execute it.

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

Update tbl_Product_Master set [Model]=2013 where [Product ID]=2

Ooopps…… Yet again, I am unable to execute it.

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

The reason behind this error is (as per MSDN) : A table with a columnstore index cannot be updated.

Resolution:
There are some best practices, by which we can post transactions or update in a columnstore index table.

Lets resolve it step by step.

Step 1 :
First of all you need find the name of the columnstore index of the particular table. (Here is the tool that will help you to find the columnstore index). Once you find the name, you need to either disable it or drop it (but in case of drop, you must keep the columnstore index creation script as well).

USE AdventureWorks2012;
GO
--To disable columnstore index
ALTER INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] DISABLE ;

--OR
USE AdventureWorks2012;
GO
--To drop columnstore index
DROP INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master]

Step 2 :

Now, try to post any transaction in the table.

USE AdventureWorks2012;
GO
insert into tbl_Product_Master values (2,'Car',2012)
--OUTPUT

(1 row(s) affected)

delete from tbl_Product_Master where [Product ID]=1
--OUTPUT

(1 row(s) affected)

Update tbl_Product_Master set [Model]=2013 where [Product ID]=2
--OUTPUT

(1 row(s) affected)

You can see that the above statements executed successfully.

Step 3 :
After posting the transactions then you need to have a look at Step 1. If you have disabled the columnstore index in Step 1 then you need to enable (rebuild) it in Step3 and if you dropped it, you need to create it.

--To enable / rebuild columnstore index
ALTER INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] REBUILD;
GO

--OR
--To create columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product Name]
, [Model],[Product ID])

Conclusion :
Whenever you come across this issue, simply you need to disable/drop the columnstore index, post the transaction and enable/create it respectively.
Remember, you cannot post any transaction in columnstore index table without disabling and dropping columnstore index.