SQL SERVER – Updating Data in A Columnstore Index

So far I have written two articles on Columnstore Indexes, and both of them got very interesting readership. In fact, just recently I got a query on my previous article on Columnstore Index. The question was about updating data in a columnstore index.

Read the following two articles to get familiar with the Columnstore Index. They will give you a reference to the question which was asked by a certain reader:

• SQL SERVER – Fundamentals of Columnstore Index
• SQL SERVER – How to Ignore Columnstore Index Usage in Query

Here is the reader’s question:

” When I tried to update my table after creating the Columnstore index, it gives me an error. What should I do?”

When the Columnstore index is created on the table, the table becomes Read-Only table and it does not let any insert/update/delete on the table. The basic understanding is that Columnstore Index will be created on the table that is very huge and holds lots of data. If a table is small enough, there is no need to create a Columnstore index. The regular index should just help it. The reason why Columnstore index was needed is because the table was so big that retrieving the data was taking a really, really long time. Now, updating such a huge table is always a challenge by itself.

If the Columnstore Index is created on the table, and the table needs to be updated, you need to know that there are various ways to update it. The easiest way is to disable the Index and enable it. Consider the following code:

USE AdventureWorks
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
( [SalesOrderDetailID])
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO 100
-- Create ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO
-- Attempt to Update the table
UPDATE [dbo].[MySalesOrderDetail]
SET OrderQty = OrderQty +1
WHERE [SalesOrderID] = 43659
GO
/* It will throw following error
Msg 35330, Level 15, State 1, Line 2
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.
*/

A similar error also shows up for Insert/Delete function. Here is the workaround. Disable the Columnstore Index and performance update, enable the Columnstore Index:

-- Disable the Columnstore Index
ALTER INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [dbo].[MySalesOrderDetail] DISABLE
GO
-- Attempt to Update the table
UPDATE [dbo].[MySalesOrderDetail]
SET OrderQty = OrderQty +1
WHERE [SalesOrderID] = 43659
GO
-- Rebuild the Columnstore Index
ALTER INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [dbo].[MySalesOrderDetail] REBUILD
GO

This time it will not throw an error while the update of the table goes successfully. Let us do a cleanup of our tables using this code:

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO
TRUNCATE TABLE dbo.MySalesOrderDetail
GO
DROP TABLE dbo.MySalesOrderDetail
GO

In the next post we will see how we can use Partition to update the Columnstore Index.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.