SQL : ColumnStore Index (part II) : Indexing

Introduction

In my first blogpost about the ColumnStore Index, I've written about creating and dropping a columnstore. Please took a look at my first blogpost: SQL : ColumnStore Index (part I). In this blogpost I'll continue and observe some details about the ColumnStore Index, particularly differences between SQL Server 2014 and SQL Server 2016 building non clustered indexes on ColumnStores.

Creating indexes on the ColumnStore 

So, the table sale (imported form WorldWideImporters demo database) is not a Rowstore anymore but a ColumnStore. Let's experiment with some indexing. Can we create a clustered index on the ColumnStore Index? It seems to not very logical (and it is) but let's try and see what happens.

       
CREATE CLUSTERED INDEX [CI_CustomerKey] ON Sale([Customer Key])
       

Resulting in :

Msg 1902, Level 16, State 3, Line 15
Cannot create more than one clustered index on table 'Sale'. Drop the existing clustered index 'CCI_Sale' before creating another.

So, this will generate error, as expected. Let's try to create a non clustered index on the ColumnStore (in SQL Server 2016)

       
CREATE NONCLUSTERED INDEX [CI_CustomerKey] ON Sale([Customer Key])

And experiment with a query and see what happens.

       
SELECT [Customer Key] FROM Sale WHERE [Customer Key] = 6
       

And now it uses the non clustered index :

But when I try to do this on SQL Server 2014, it will give an error:

Msg 35303, Level 16, State 1, Line 1
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.

So, creating a (unique) non clustered index on SQL Server 2014 is not possible but on SQL Server 2016 it is easy done.

Conclusion

It is not possible to create (unique) non clustered indexes on a Clustered ColumnStore in SQL Server 2014. In SQL Server 2016 it is a piece of cake.

Greetz,

Hennie