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

Continuation from the previous 63 parts, the whole series can be found at http://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:

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:

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:

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:

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:

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:

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:

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:

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 🙂

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

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

  1. Duncan Morgan

    Hi Niko
    In the section ‘creating a In-Memory table with Clustered Columnstore Index’ – what version of SQL does your table creation script work in? my original attempt and pasting in yours in my azure VM with SQL 13.0.2.00 gives ‘incorrect syntax neat ‘)’ and same error for MEMORY_OPTIMIZED
    Thanks
    Duncan

    1. Niko Neugebauer Post author

      Hi Duncan,

      Your VM is a CTP 2.0, while this syntax was added to later builds.
      Consider upgrading your VM to CTP 2.4, which is already available for a week as I am writing this answer.

      Best regards,
      Niko Neugebauer

Leave a Reply

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