Updating Non-Updatable Columnstore Indexes

Niko Neugebauer shows how to load data into non-updated non-clustered columnstore indexes:

I decided to make a serious step back and write about something that is concerning the current (SQL Server 2014) and the elder version of SQL Server that supports Nonclustered Columnstore Indexes – (SQL Server 2012).
The Nonclustered Columnstore Indexes in SQL Server 2012 & 2014 are non-updatable, meaning that after they are built on the table, you cannot modify the table anymore – you can only read the data from it.
The common solutions for this problem are:
– Using Partitioning
– Disabling Columnstore, modifying the data and Rebuilding the Columnstore Index then (thus activating it)

Sounds easy, doesn’t it ?
Well, like with everything in the real life, there are a couple of quite important gotchas here.

The “non-updatable” part is why I ignored non-clustered columnstore indexes.  With SQL Server 2016, I’m going to take another look at them.  But if you’re living on 2012 or 2014 for a while, this is a good post to give you an idea of how to load those tables.

Related Posts

Columnstore And Merge Replication

Niko Neugebauer tests whether merge replicated tables can use columnstore indexes: Adding this table to the publication will end up with the following, self-explaining error message, being very clear that the Clustered Columnstore Indexes are not supported for the Merge Replication[.] There is no surprise here, as the same Clustered Columnstore Indexes are not supported […]

Read More

Trace Flag 834 And Columnstore Tables

Joe Obbish shows how trace flag 834 can solve a bottleneck when inserting into tables with clustered columnstore indexes: In my experience, when we get into a situation with high memory waits caused by too much concurrent CCI activity all queries on the server that use a memory grant can be affected. For example, I’ve […]

Read More


March 2016
« Feb Apr »