Columnstore Index Enhancement in SQL 2016

SQL Server 2012 introduced the "Columnstore" indexes , which was intended for data warehousing applications. Before than , the indexes were of "rowstore" ( ie , regular indexes that we have seen from inception ).

In SQL Server 2012 , this feature was not widely adapted , as the table was not updatable with the column store index on . To load the data , we need to drop the index , load the data and recreate the index . Not all the business can afford to do this. 


In SQL Server 2014 , we were to able to update the table , if it had clustered columnstore index. This will be only index in this table. Little bit better .  We were able to add any other indexes such as non clustered primary key to enforce the unique constraints on the underlying data set or some other index that will speed up other processes.  

In SQL Server 2016 , things get better. Table having clustered column store index , can have ONE non clustered index .  Hopefully , the limit of ONE will be released in the next releases. 


Here is an example in SQL Server 2014 

1>  Select @@version
2> go

-
Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
        Jun 10 2015 03:35:45
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 10586: )


(1 rows affected)
1> Create Table Sales
2> ( OrderID int not null ,
3>   DateID  int  not null ,
4>   BuyerID int  not null ,
5>   ProductID int  not null ,
6>   Quantity int  not null ,
7>   Price numeric(7,2)  not null
8>   ) ;
9> go

1>
2> CREATE CLUSTERED COLUMNSTORE INDEX ccidx_Sales ON Sales  ;
3> go

1>
2>
3> ALTER TABLE Sales ADD CONSTRAINT t_Sales_pk  PRIMARY KEY nonclustered (OrderID,ProductID) ;
4> go

Msg 35303, Level 16, State 1, Server XXXXXX, Line 3
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.
Msg 1750, Level 16, State 1, Server XXXXXX, Line 3

Could not create constraint or index. See previous errors.


Let us run the same scripts in SQL Server 2016 . 


4> Create Table Sales
5> ( OrderID int not null ,
6>   DateID  int  not null ,
7>   BuyerID int  not null ,
8>   ProductID int  not null ,
9>   Quantity int  not null ,
10>   Price numeric(7,2)  not null
11>   ) ;
12> go

1> CREATE CLUSTERED COLUMNSTORE INDEX ccidx_Sales ON Sales  ;
2> go

1>
2>  ALTER TABLE Sales ADD CONSTRAINT t_Sales_pk  PRIMARY KEY nonclustered (OrderID,ProductID) ;

3> go


Now , we are able to have the table with a clustered columnstore index and one non clustered index.