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. :)

Msg 35330, Level 15, State 1, Line 36
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

The error message says that it can’t be done – that was exactly what we expected.

Let’s follow the path of disabling and re-enabling of the columnstore indexes:

Boom!Msg 35330, Level 15, State 1, Line 5
DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

But, but !!! All the demos, all the cool stuff, all the documentations…

– Do not give up, cause we have got RECOMPILE!
– Recompile!?
– Yes, Recompile! SQL Server analises your script at the moment you are submitting it, and once it detects something that won’t work in it’s analysis, it will give you an error.
– But, but, but how to solve it ? With Recompile ?
– Yes, with Recompile. Simply add OPTION(RECOMPILE) to your statements, forcing SQL Server to analise your command at the moment of execution:

Boom!Msg 35349, Level 16, State 1, Procedure LoadDataInto_NCCI_Table, Line 81
TRUNCATE TABLE statement failed because table ‘FactOnlineSales_NCCI_ReadOnly’ has a columnstore index on it. A table with a columnstore index cannot be truncated. Consider dropping the columnstore index then truncating the table.

You can not truncate a table with a Columnstore Index (Nonclustered actually, but that’s ok – in SQL Server 2012 there were no other options).
What can we do ?

We can:
– use a DELETE command
– we can migrate to SQL Server 2014 and start using Clustered Columnstore Index
– we can migrate to Azure SQLDatabase or SQL Server 2016 where Nonclustered Columnstore Indexes are very much updatable
:)

Final Thoughts

If you are working with a Nonclustered Columnstore Index in SQL Server 2012 or in SQL Server 2014, be careful when you are programming – recompile the DML statements(with exception of truncate, which should be avoided) working with the table that has Nonclustered Columnstore.
If you want the real performance, then using Partitioning – and switch partitions in and out, you can use truncate on the heaps without any problems :)