Columnstore Indexes in SQL Server: When CCI Beats NCCI for Analytics

A fact table that scans too much

Monthly sales aggregation on an 800M row fact table times out in the BI layer. The query filters a few columns and sums two others. Rowstore reads every page even for columns it never uses. The useful takeaway is that a columnstore index can cut I/O and enable batch mode, but the right form depends on whether the table must stay transactional.

Columnstore stores data by column in compressed rowgroups instead of by row. That reduces I/O for wide scans and aggregations. SQL Server processes those rowgroups in batch mode, handling many rows per CPU operation instead of row-by-row.

Clustered vs Nonclustered layout

A Clustered Columnstore Index, CCI, makes the whole table columnstore. It provides the highest compression and is the default choice for read‑mostly analytical tables. It requires SQL Server 2012 or later for CCI. All data is stored in columnstore segments, with no rowstore clustered key.

A Nonclustered Columnstore Index, NCCI, lives alongside a rowstore clustered or heap index. The rowstore remains the transactional access path, while the NCCI accelerates analytical scans. NCCI is supported on SQL Server 2016 and later. This hybrid model lets you keep OLTP patterns and add columnstore acceleration without converting the base table.

Data is organized into rowgroups of about 1 million rows. New inserts and updates go to a deltastore, an open rowgroup in rowstore format, before they are compressed into closed rowgroups. That deltastore is why small, constantly changing tables can be slower with columnstore.

Worked example: add NCCI to an existing fact table

Run in SSMS connected to the target database. The principal needs db_owner or ALTER permission on the table. The operation takes a schema modification lock and can be memory intensive on large tables.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_FactSales
ON dbo.FactSales (SaleDate, Amount, Quantity, RegionKey)
WITH (DROP_EXISTING = OFF, ONLINE = OFF);

Placeholders: dbo.FactSales is the rowstore fact table, IX_CS_FactSales is the index name, and the column list is optional but helps the optimizer. Risk: building on a hot table can block writes. For very large tables consider building during a maintenance window or using partition switching.

After creation, verify rowgroup health. Run as a member of db_datareader.

SELECT 
    row_group_id,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSales');

Expected checks: most rowgroups should show state_desc = COMPRESSED with total_rows near 1,048,576. OPEN or COMPRESSING states indicate the deltastore is still active. High deleted_rows suggests fragmentation from updates.

Confirm batch mode is used. Compare the actual execution plan for the aggregation query with and without the index. Look for Columnstore Index Scan with Batch Mode in the plan operators. Higher memory grants are normal for batch mode.

Trade‑offs and limits

Updating data in a columnstore is expensive. Updates mark rows as deleted in the deltastore and insert new versions. Frequent single‑row updates can cause many small open rowgroups and reduce compression.

Small tables rarely benefit. Columnstore metadata and segment overhead can outweigh gains when the table is under a few million rows or when queries are highly selective point lookups.

Memory grants for batch mode queries can be significantly higher than row‑mode queries. This can lead to resource semaphore waits under concurrent load. Monitor sys.dm_exec_query_memory_grants.

Measure disk space reduction by comparing the size of the rowstore table versus the columnstore index using sys.dm_db_partition_stats. Compression gains depend on data cardinality and repetition.

Actionable closing

Use CCI for large, append‑only analytical tables where maximum compression and scan speed matter. Use NCCI for hybrid workloads that need transactional rowstore access with analytical acceleration.

Before committing, check rowgroup compression, batch mode usage in plans, and memory grant impact on your workload. Rebuild or reorganize columnstore indexes during maintenance to compress open deltastore rowgroups.