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.

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:

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Updating Data in A Columnstore Index

  1. Pingback: SQL SERVER – Video – Performance Improvement in Columnstore Index « Journey to SQLAuthority

  2. Pingback: SQL SERVER – Identify Columnstore Index Usage from Execution Plan « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  4. Hi, my need is to disable columnstore index and update the table. But this operation has to be done in a Stored procedure. As we cannot use Go statement in SP, I am getting error for disabling the index before updating the table, it is happening because queries are not executing in batches.
    Any workaround for this?

    Like

    • Hi Gaurav,
      Try using Dynamic SQL in your stored procedure:
      Create Procedure SP
      As
      Begin
      ……
      EXEC (‘ALTER INDEX idx ON dbo.tbl DISABLE;’)
      EXEC(‘INSERT INTO dbo.tbl …;’)
      ALTER INDEX idx ON dbo.tbl REBUILD;

      End
      Regards,

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s