Clustered Columnstore index

How to create a Clustered Columnstore index in SQL Server 2014 ?

The recent release of SQL Server 2014 (CTP1 version) is providing the “updatable Columnstore indexes” which is a new type of Index introduced with SQL Server 2012 which improves the query performance in OLTP environments.

In brief, the columnstore technology is different from the conventional B-Tree indexes which uses the row-store mechanism to maintain the indexes. In a columnstore indexes, the data from each column is stored in a separate set of disk pages which can make the queries to run faster. But, in SQL Server 2012, the biggest problem with this index is that this is a read-only index which means that updates to the underlying data is not allowed when there is a columnstore index created on the table. Also, in SQL Server 2012, a columnstore index is just a non-clustered. Before I installed the SQL Server 2014 CTP1, I was in the impression that this is still just the Non-Clustered Columnstore index but now this can be updated when the underlying data is changed. I just wanted to check how this works and to my surprise, there is an option to create a CLUSTERD Columnstore index available which is shown in Figure 1. I have downloaded the AdventureworksDW2012 sample database and attached to the SQL Server 2014 CTP1 instance I installed on my machine. I have then chosen the largest table "FactProductInventory" from this database to create a clustered Columnstore index. But, it has shown me an error saying that this table doesn’t satisfy the pre-requisites for the clustered columnstore index. The reason being for that was the table I have chosen has foreign key constraints associated with it. So, I just have imported the data to a new table with name "FactProductInventory1" with no foreign key constraints to create the clustered columnstore index.  



Figure 1: Creating Clustered Columnstore index in SQL Server 2014.

 
As I have marked in the Figure 1 above, we can see multiple index types for the columnstore indexes are available (clustered and non-clustered) in SQL Server 2014. I have observed few constraints about creating these and please refer the limitations section below about them.

Unlike the non-clustered columnstore indexes or B-Tree indexes, for the clustered columnstore index there is no option to choose a particular column for the cluster key. This indicates that clustered columnstore index will have all the columns stored in it. And, remember that we can create only one clustered columnstore index on a table which is similar to that of the B-Tree clustered index.



Figure 2: Index creation page.

 

The below figure 3 represents how the clustered columnstore index looks like in the Indexes section of a table in the SQL Server Management Studio.



Figure 3: Columnstore index of clustered type created.

 

Limitations I have observed for the Clustered Columnstore index:

1. We can’t have the one if the table is having any foreign key constraints associated.
2. Only one clustered column store index is possible similar to that of conventional row store clustered index.
3. Clustered columnstore index can’t be created when we have a clustered row store index.
4. No possibility to create the clustered Columnstore index on selected columns.
5. No non-clustered Columnstore index is allowed if we have a clustered columnstore index on a table.

 
-----------------------------------------------------------------------------------------------------------------