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:

USE [master]
 
alter database ContosoRetailDW
    set SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
RESTORE DATABASE [ContosoRetailDW] 
    FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
        MOVE N'ContosoRetailDW2.0' TO N'C:\Data\ContosoRetailDW.mdf', 
        MOVE N'ContosoRetailDW2.0_log' TO N'C:\Data\ContosoRetailDW.ldf',  
        NOUNLOAD,  STATS = 5;
 
alter database ContosoRetailDW
    set MULTI_USER;
GO
GO
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0', SIZE = 1000000KB , FILEGROWTH = 128000KB )
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0_log', SIZE = 400000KB , FILEGROWTH = 256000KB )
GO

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

use ContosoRetailDW;
CREATE TABLE FactOnlineSales_NCCI_ReadOnly(
[OnlineSalesKey] [int] NOT NULL,
[DateKey] [datetime] NOT NULL,
[StoreKey] [int] NOT NULL,
[ProductKey] [int] NOT NULL,
[PromotionKey] [int] NOT NULL,
[CurrencyKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[SalesOrderLineNumber] [int] NULL,
[SalesQuantity] [int] NOT NULL,
[SalesAmount] [money] NOT NULL,
[ReturnQuantity] [int] NOT NULL,
[ReturnAmount] [money] NULL,
[DiscountQuantity] [int] NULL,
[DiscountAmount] [money] NULL,
[TotalCost] [money] NOT NULL,
[UnitCost] [money] NULL,
[UnitPrice] [money] NULL,
[ETLLoadID] [int] NULL,
[LoadDate] [datetime] NULL,
[UpdateDate] [datetime] NULL
);
create nonclustered columnstore index NCCI_FactOnlineSales_ReadOnly
    on dbo.FactOnlineSales_NCCI_ReadOnly (OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate);

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:

insert into FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales;

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:

alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
disable;

Lets execute the process of loading the data

delete from dbo.FactOnlineSales_NCCI_ReadOnly;
insert into dbo.FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales
option (recompile);

And now enable the index by rebuilding it.

alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
rebuild;

It runs perfectly !

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

alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
disable;
delete from dbo.FactOnlineSales_NCCI_ReadOnly;
insert into dbo.FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales;
alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
rebuild;

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:

alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
disable;
delete from dbo.FactOnlineSales_NCCI_ReadOnly
option (recompile);
insert into dbo.FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales
option (recompile);
alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
rebuild;

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:

create procedure dbo.LoadDataInto_NCCI_Table as
begin
if( exists (select 1 from sys.indexes i 
where i.object_id = object_id('dbo.FactOnlineSales_NCCI_ReadOnly', 'U')
and i.name = 'NCCI_FactOnlineSales_ReadOnly' ) )
alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
disable;
delete from dbo.FactOnlineSales_NCCI_ReadOnly
option (recompile);
insert into FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales
option (recompile);
create nonclustered columnstore index NCCI_FactOnlineSales_ReadOnly
on dbo.FactOnlineSales_NCCI_ReadOnly (OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate);
end

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

exec dbo.LoadDataInto_NCCI_Table;

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:

alter procedure dbo.LoadDataInto_NCCI_Table as
begin
if( exists (select 1 from sys.indexes i 
where i.object_id = object_id('dbo.FactOnlineSales_NCCI_ReadOnly', 'U')
and i.name = 'NCCI_FactOnlineSales_ReadOnly' ) )
alter index NCCI_FactOnlineSales_ReadOnly 
on dbo.FactOnlineSales_NCCI_ReadOnly
disable;
truncate table dbo.FactOnlineSales_NCCI_ReadOnly;
insert into FactOnlineSales_NCCI_ReadOnly
select top 150000 OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate
from dbo.FactOnlineSales
option (recompile);
create nonclustered columnstore index NCCI_FactOnlineSales_ReadOnly
on dbo.FactOnlineSales_NCCI_ReadOnly (OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate);
end

Let’s test it:

exec dbo.LoadDataInto_NCCI_Table;

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”)