What is the difference between a clustered and a nonclustered columnstore index?
Quick Answer
A clustered columnstore index is the table's storage — the whole table stored column-wise; best for a large fact/warehouse table that's mostly bulk-loaded and scanned. A nonclustered columnstore index is a secondary column-wise copy of chosen columns on top of a normal rowstore table, enabling analytical queries on an OLTP table without a separate warehouse (HTAP).
A clustered columnstore index means the table itself is stored by column instead of by row — there's no B-tree heap underneath, that's the primary storage. You use it for a big fact table in a warehouse that gets loaded in batches and queried with big aggregations. A nonclustered columnstore is an additional column-oriented index sitting on a regular rowstore table. That's the interesting one for HTAP: your transactional writes keep going to the rowstore at full speed, but reporting queries get answered from the columnstore copy, so you can run analytics on the live database without standing up a separate system. Both get you compression, batch mode, and rowgroup elimination.
- Both give huge compression and batch-mode execution for aggregations; rowgroup/segment elimination skips whole chunks by min/max.
- Clustered CCI: the table has no rowstore; add nonclustered B-tree indexes for the occasional point lookup.
- Nonclustered CCI: keeps OLTP writes on the rowstore, analytics hit the columnstore; can be filtered to just “warm” rows.
- Small trickle inserts land in a delta store until a rowgroup fills (~1M rows) then compress.