Columnstore Indexes – part 79 (“Loading Data into Non-Updatable Nonclustered Columnstore”)

Continuation from the previous 78 parts, the whole series can be found at http://www.nikoport.com/columnstore/.

Update: In SQL Server 2016 the Nonclustered Columnstore Indexes are UPDATABLE, and you can do any updates without any troubles, for more please read Columnstore Indexes – part 69 (“Operational Analytics – Disk Based”) & Columnstore Indexes – part 55 (“New Architecture Elements in SQL Server 2016”)

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. 🙂

For this blogpost besides my favourite test database ContosoRetailDW, I will also use an instance of SQL Server 2012:

With the database restored, let’s create a copy of the dbo.FactOnlineSales table and add a Nonclustered Columnstore Index on it:

Let’s see if loading data directly into this table with SQL Server 2012 works, let’s add 150.000 rows from the original table:

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:

Lets execute the process of loading the data

And now enable the index by rebuilding it.

It runs perfectly !

Ok, let’s execute it all together in 1 script:

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:

Here is the result of another successful execution:

(150000 row(s) affected)

(150000 row(s) affected)

That’s magnificent!
I would like to automate this process, which will do the full load on a regular basis, and so we need to create a stored procedure:

We can execute the stored procedure at any moment at our convenience:

But wait a second, I do not want to do the DELETE operation every time, cause my table is really big. Let’s do TRUNCATE, cause I am doing a Full Load:

Let’s test it:

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 🙂

It’s not very complicated, after all 🙂

to be continued with Columnstore Indexes – part 80 (“Local Aggregation”)

2 thoughts on “Columnstore Indexes – part 79 (“Loading Data into Non-Updatable Nonclustered Columnstore”)

Leave a Reply

Your email address will not be published. Required fields are marked *