Columnstore Indexes – part 64 (“T-SQL Improvements in SQL Server 2016”)

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

I have decided to write a rather small blogpost, highlighting the T-SQL improvements already available in SQL Server 2016.
The reason behind this blogpost is because I am a lazy guy and any improvement that spares keyboard typing and potential mistakes is something that I always welcome with a lot of joy. :)
At the moment of this article writing, with SQL Server 2016 CTP 2.3, there are just 2 major improvements that are available to my knowledge, but I will keep an eye on the matter and will update this article, should there be more T-SQL functionalities making their way into the product before or after the RTM.

Inline Table definition of Clustered Columnstore Index

From SQL Server 2016 you can have the DWH Columnstore Definition made with just 1 statement, because starting with this version we can declare Clustered Columnstore Indexes in the table definition.
Consider the following example where after listing all available columns in our table, we can simply add a comma(,) writing that we are adding an index that will be a Clustered Columnstore:

CREATE TABLE [dbo].[FactOnlineSales_CCI](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     INDEX PK_FactOnlineSales_CCI CLUSTERED COLUMNSTORE 
);

This script executes beautifully on SQL Server 2016 CTP 2 without any errors, defining the whole table and our Clustered Columnstore Index the way we exactly wanted.
Running the very same script on SQL Server 2014 SP1 with CU1 produces the following error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

The improvement does not stop just here, since we can declare more indexes or constraints after the clustered columnstore, but I will not go into the details of SQL Server 2016 Clustered Columnstore compatibility improvements in the next blog post.

Inline Table definition of Nonclustered Columnstore Index

The solution for the Operational Analytics in SQL Server is the new updatable Nonclustered Columnstore Index, which in SQL Server 2016 can be defined inline, in the same way that Clustered Columnstore Index is defined, as shown above:

CREATE TABLE [dbo].[FactOnlineSales_NCCI](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     INDEX IX_FactOnlineSales_NCCI Nonclustered Columnstore ([OnlineSalesKey],[StoreKey],[ProductKey],[PromotionKey],[CurrencyKey],[CustomerKey])
);

The execution on SQL Server 2016 CTP 2 is successful, but if we execute the very same code on SQL Server 2014, we shall get the following error message:

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'Columnstore'.

Another important improvement in SQL Server 2016 is the fact that we can define an updatable Filtered Nonclustered Columnstore Index, and hey – it works with inline as well:

CREATE TABLE [dbo].[FactOnlineSales_NCCI_Filtered](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     INDEX IX_FactOnlineSales_NCCI_Filtered 
		Nonclustered Columnstore ([OnlineSalesKey],[StoreKey],[ProductKey],[PromotionKey],[CurrencyKey],[CustomerKey])
			where OnlineSalesKey > 1000
);

Coming back to the bigger picture – why I am separating these 2 options ?
They contain different syntax that leads to different functionalities, and in the next section you will see how it has reflections across different functional parts of SQL Server 2016.

In-Memory OLTP & Columnstore Indexes

In SQL Server 2016 we have the opportunity to join 2 new worlds of SQL Server – InMemory OLTP (Hekaton) and Columnstore Indexes, by creating a InMemoryTable that contains a Columnstore Index, and check it out – it can be done inline:

ALTER DATABASE [ContosoRetailDW] 
	ADD FILEGROUP [ContosoRetailDW_Hekaton] 
	CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE [ContosoRetailDW]
	ADD FILE(NAME = ContosoRetailDW_HekatonDir, 
	FILENAME = 'C:\Data\xtp') 
	TO FILEGROUP [ContosoRetailDW_Hekaton];

GO
CREATE TABLE [dbo].[FactOnlineSales_Hekaton](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     Constraint PK_FactOnlineSales_Hekaton PRIMARY KEY NONCLUSTERED ([OnlineSalesKey]),
     INDEX CCI_FactOnlineSales_Hekaton 
		CLUSTERED COLUMNSTORE 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

I strongly suspect that the inclusion of the inline definition for the Columnstore Indexes has a lot to do with the fact that like in SQL Server 2014 we can’t change Hekaton table once it is defined, in SQL Server 2016 it looks that we can change Hekaton tables, but as long as they do not include Columnstore Index.
Trying to execute any simple command that will change a column on our In-Memory table:

-- Let's change anything :)
alter table dbo.FactOnlineSales_Hekaton
	add c1 int null;

is instantly provoking a following error message:
Msg 10794, Level 16, State 15, Line 52
The operation ‘ALTER TABLE’ is not supported with memory optimized tables that have a column store index.
We better start learning how to define Columnstore Indexes online, because I do not expect this to change for the whole 2016 release.

Ok, Niko – but what about a Nonclustered Columnstore Index for InMemory OLTP ?
Let’s try to define it:

drop table [dbo].[FactOnlineSales_Hekaton];

CREATE TABLE [dbo].[FactOnlineSales_Hekaton](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     Constraint PK_FactOnlineSales_Hekaton PRIMARY KEY NONCLUSTERED ([OnlineSalesKey]),
     INDEX CCI_FactOnlineSales_Hekaton 
		NONCLUSTERED COLUMNSTORE ([OnlineSalesKey])
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

This time around we will not have a successful execution, because Nonclustered Columnstore Indexes for InMemory OLTP tables are not supported in this release:
Msg 10794, Level 16, State 74, Line 41
The feature ‘NONCLUSTERED COLUMNSTORE’ is not supported with memory optimized tables.
I would absolutely love this to change, but unless we see them in CTP 3 around PASS Summit (this is my personal speculation), we might have to wait until 2018 to get them.

Extra Items:

Before CTP 2.3 executing the following Alter Index … Reorganize command was provoking error, but in the latest release it has been corrected and already does what one should expect from it – close all open Delta-Stores :)

alter index PK_FactOnlineSales_CCI on [dbo].[FactOnlineSales_CCI]
	Reorganize with (COMPRESS_ALL_ROW_GROUPS = ON);

to be continued with Columnstore Indexes – part 65 (“Clustered Columnstore Improvements in SQL Server 2016”)