What are the differences between Clustered/Nonclustered Columnstore Indexes?

    For Business Intelligence, Columstore indexes are one of the best features of SQL Server. You can run your reports or analytic queries in OLTP tables with Columnstore indexes. That means you will have real time data available in your reports. To get the benefits of Columnstore indexes, you should have at least 1 million rows in your table! There are two types of columnstore indexes.  

• Clustered Columnstore Indexes (CCI)
• NonClustered Columnstore Indexes (NCCI)     

     Let's look at the Clustered Columnstore type first. CCI is used in data warehouse like structures, main reason for that is, when you create a clustered columnstore you index the whole table.You need to be sure that all of the column data types of the table are supported by columnstore indexes! For example varbinary types,  like geometry, hierarchyid are not supported by columnstore and you can not create any type of columnstore index on them! CCI will save you storage space too. CCI compress rate is 10x, compression rate depends on your table's schema and its data but you can expect somewhere between 9 to 10 times. If your table is already PAGE compressed then you can expect compression rate between 4 to 5 times. CCI type will not help to your OLTP queries!

    NCCI is different, you need to specify the columns when you create NCCI types. You should include only the columns needed for analytics when you create NCCI type. Don't forget Columstore Indexes are not going to help your OLTP queries. Unlike CCI types NCCI does not save you any space, you might require 10% more storage as a new index. Hopefully, you might be able to drop couple of row store indexes after you create the NCCI in your tables. With NCCI, you can run your OLTP and analytic queries in the same database.

    SQL Server 2017 added new functions to columnstore indexes. You can include computed columns in the columnstore indexes now. Also columnstore indexes started to support varchar(max), nvarchar(max) and nvarbinary(max) in SQL Server 2017. This is great news because with nvarchar(max) support, we can index the JSON values with columnstore indexes.