Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index

SQL Server 2016 provides two flavors of columnstore index; clustered (CCI) and nonclustered (NCCI) columnstore index. As shown in the simplified picture below, both indexes are organized as columns but NCCI is created on an existing rowstore table as shown on the right side in the picture below while a table with CCI does not have a rowstore table. Both tables can have one or more btree nonclustered indexes.

cci-ncci

Other than this, the physical structures on how data is stored in delta and compressed rowgroups are identical and both kinds of indexes have same performance optimizations including batchmode operators. However, there some  key differences between these and the table below lists the main differences.

cci-vs-ncci-diff

Thanks

Sunil Agarwal