Columnstore Indexes for Fact Tables: When and Why

Take the FactSales table from Fact and Dimension Tables: How They Join Together, millions of rows, a handful of narrow numeric columns, queries that aggregate across huge swaths of it and rarely touch every column at once. That description is, almost word for word, the use case columnstore indexes were built for. If you have exactly one table in your database where columnstore is the obvious right call, it's the fact table.

Why Row Storage Fights the Workload

A traditional rowstore index stores an entire row together on a page: every column of that FactSales row, needed or not, gets read together. A query that sums SalesAmount grouped by month still has to read ProductKey, CustomerKey, DiscountAmount, and every other column along with it, because that's how the data is physically laid out.

A columnstore index flips that: each column is stored separately, compressed, in its own set of pages. A query touching three columns out of twelve only reads the storage for those three, the rest of the table's I/O simply doesn't happen. For a wide fact table and a narrow aggregation query, that difference is often an order of magnitude in I/O alone, before compression benefits are even counted.

	Rowstore	Columnstore
Best for	OLTP: point lookups, single-row inserts/updates	Analytics: aggregating millions of rows across few columns
Compression	Page/row compression, moderate	Much higher; similar values across a column compress extremely well
Execution mode	Row-at-a-time processing	Batch mode: processes ~900 rows at a time per operator call, dramatically less CPU overhead
Single-row updates	Efficient, direct page modification	Expensive relative to rowstore; lands in the delta store first (see below)

Batch Mode: The Other Half of the Story

Columnstore's I/O reduction gets most of the attention, but batch mode execution is just as significant. Instead of the query engine processing one row at a time through each operator (row mode), batch mode processes roughly 900 rows together as a unit, cutting per-row CPU overhead dramatically for the scans, aggregations, and joins typical of a star-schema query. This is why a columnstore-backed query can be faster even when the underlying I/O reduction alone wouldn't fully explain the gap, it's not just reading less data, it's processing what it does read more efficiently.

Rowgroups and the Delta Store

Columnstore data is organized into rowgroups, each holding up to about a million rows, compressed independently. New rows don't go straight into compressed columnstore format, they land first in a delta store, an ordinary rowstore structure, until enough accumulate (or a background process compresses them) to form a new compressed rowgroup.

A columnstore index with a large, uncompressed delta store gives you none of the benefit. If rows trickle in continuously in small batches rather than large bulk loads, the delta store can stay large and rowstore-like indefinitely, silently undermining the compression and batch-mode gains you built the index for. Run ALTER INDEX ... REORGANIZE periodically to force delta store rows into compressed rowgroups, and prefer bulk-loading fact table inserts (a single large batch load, matching the set-based staging pattern already recommended for dimension loads) over frequent small transactional inserts.

-- Check how much of the table is still sitting in the (uncompressed) delta store
SELECT
    object_name(object_id)   AS TableName,
    state_description,
    total_rows,
    deleted_rows
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSales')
ORDER BY state_description;

-- Force delta store rows into compressed rowgroups
ALTER INDEX CCI_FactSales ON dbo.FactSales REORGANIZE;

Clustered vs. Nonclustered Columnstore

Clustered columnstore (CCI): The table's primary storage is columnstore. Best when the table is overwhelmingly used for analytics and doesn't need traditional rowstore-style point lookups or a conventional clustered index.
Nonclustered columnstore (NCCI): Adds a columnstore index alongside the existing rowstore table, giving analytic queries the columnstore/batch-mode benefit while OLTP-style point queries keep using the rowstore structure underneath. Useful when the same fact table genuinely serves both patterns.

Common Gotchas

Applying it to a dimension table instead of the fact table. Dimensions are usually small and read via lookups/joins on a few key columns, exactly the pattern rowstore handles well; columnstore's advantage is largest on the wide, high-row-count fact table, not the small dimensions around it.
Frequent single-row updates against a clustered columnstore. Each update effectively becomes a delete-plus-insert into the delta store; a fact table with heavy row-by-row updates (rather than append-mostly inserts) fights the structure instead of benefiting from it.
Ignoring rowgroup quality. A rowgroup with a high percentage of deleted rows (from updates or deletes) still occupies space and hurts scan efficiency until it's rebuilt; sys.dm_db_column_store_row_group_physical_stats surfaces this directly.
Expecting the same gains on a small table. Columnstore's advantages compound with row count and column width; on a fact table with a few thousand rows, the compression and batch-mode benefits are real but rarely worth the added complexity over a good rowstore index.

The Bottom Line

A fact table built with a clear grain, additive measures, and foreign keys to a handful of dimensions is exactly what columnstore was designed around. The gains come from two different mechanisms working together, far less I/O from column-oriented storage, and far less CPU per row from batch mode execution, but only if data actually lands in the compressed rowgroup structure rather than sitting in the delta store. Bulk-load in large batches, reorganize periodically, and the fact table becomes the one place in the schema where columnstore is close to a default choice rather than a judgment call.

← Back to Blog