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.