Press "Enter" to skip to content

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.