When Columnstore Indexes Actually Pay Off in SQL Server

The Problem: Scans That Never Finish

You have a fact table with tens of millions of rows. A simple aggregation — sum of sales by region and quarter — takes minutes. The execution plan shows a clustered index scan with a Hash Match aggregate, and SET STATISTICS IO reports thousands of logical reads. You’ve added covering indexes, updated statistics, even partitioned the table, but the scan cost remains.

Columnstore indexes change the physics of that scan. Instead of reading rows page by page, SQL Server reads compressed column segments in batches of ~900 rows, using vectorized CPU instructions. The same query can drop from minutes to seconds — but only if the workload and data shape match the feature’s sweet spot.

How Columnstore Changes the Scan

A clustered columnstore index (CCI) stores data in row groups of up to 1,048,576 rows, each compressed per column. When the optimizer chooses a plan, it can execute in batch mode: operators process a vector of values at once rather than row-by-row. This reduces CPU cycles per row and, because columns are compressed, slashes I/O.

SQL Server 2019 introduced batch mode on rowstore, letting the optimizer use batch-mode operators even on traditional B-tree tables when the estimated row count crosses an internal threshold (roughly 100,000 rows). You get some vectorization benefit without converting the table. SQL Server 2022 added memory-optimized columnstore, removing the 10 GB memory grant cap that could spill large fact queries to tempdb.

Worked Example: 5 Million Row Fact Table

Create a test table and load 5 million rows (run in a non-production database, requires db_owner or ddl_admin):

CREATE TABLE dbo.FactSales (
    SaleKey BIGINT IDENTITY(1,1) NOT NULL,
    SaleDate DATE NOT NULL,
    RegionID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice MONEY NOT NULL,
    DiscountPct DECIMAL(5,2) NOT NULL
);
GO

-- Populate with 5M rows (adjust loop for speed)
DECLARE @i INT = 0;
WHILE @i < 50
BEGIN
    INSERT INTO dbo.FactSales (SaleDate, RegionID, ProductID, Quantity, UnitPrice, DiscountPct)
    SELECT TOP (100000)
        DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1095, '2020-01-01'),
        ABS(CHECKSUM(NEWID())) % 50 + 1,
        ABS(CHECKSUM(NEWID())) % 1000 + 1,
        ABS(CHECKSUM(NEWID())) % 20 + 1,
        CAST((ABS(CHECKSUM(NEWID())) % 5000 + 100) / 100.0 AS MONEY),
        CAST((ABS(CHECKSUM(NEWID())) % 30) / 100.0 AS DECIMAL(5,2))
    FROM sys.objects a CROSS JOIN sys.objects b;
    SET @i += 1;
END
GO

First, run the aggregation with a traditional clustered B-tree index:

CREATE CLUSTERED INDEX CX_FactSales_SaleKey ON dbo.FactSales (SaleKey);
GO

SET STATISTICS IO, TIME ON;
SELECT RegionID, YEAR(SaleDate) AS SaleYear, SUM(Quantity * UnitPrice * (1 - DiscountPct)) AS Revenue
FROM dbo.FactSales
GROUP BY RegionID, YEAR(SaleDate);
GO

SET STATISTICS IO, TIME OFF;

Note logical reads, CPU time, and elapsed time. Then drop the B-tree and create a clustered columnstore index:

DROP INDEX CX_FactSales_SaleKey ON dbo.FactSales;
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;
GO

SET STATISTICS IO, TIME ON;
SELECT RegionID, YEAR(SaleDate) AS SaleYear, SUM(Quantity * UnitPrice * (1 - DiscountPct)) AS Revenue
FROM dbo.FactSales
GROUP BY RegionID, YEAR(SaleDate);
GO

SET STATISTICS IO, TIME OFF;

Check the execution plan for the second run. You should see a Columnstore Index Scan operator with Batch Mode = true. Logical reads typically drop by an order of magnitude because only the three referenced columns (RegionID, SaleDate, Quantity, UnitPrice, DiscountPct) are read, and they’re compressed.

Verify the index type and configuration:

SELECT i.name, i.type_desc, i.index_id
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.FactSales');

SELECT * FROM sys.column_store_indexes WHERE object_id = OBJECT_ID('dbo.FactSales');

Where Columnstore Hurts

• High-frequency single-row DML: INSERT/UPDATE/DELETE on a CCI writes to a delta store (a hidden B-tree). Frequent small modifications fragment row groups, forcing background tuple-mover merges or manual ALTER INDEX ... REORGANIZE. OLTP workloads with many singleton writes will degrade.
• Bulk load downtime: The fastest bulk load into a CCI uses BULK INSERT or INSERT ... SELECT with TABLOCK directly into the columnstore, but if the table already has a CCI, you must drop and rebuild it for maximal compression — causing downtime. A common pattern: stage into a heap, create CCI, then swap via partition switch.
• Version gates: Batch mode on rowstore requires SQL Server 2019 (15.x) or later. Memory-optimized columnstore requires SQL Server 2022 (16.x) and Enterprise/Developer edition. Confirm @@VERSION before designing around these features.

Decision Checklist

1. Is the table primarily read-heavy analytical (scans, aggregations, star-schema joins)?
2. Does it exceed ~1 million rows, or do queries regularly process >100k rows?
3. Can you tolerate batch-oriented maintenance (rebuild/reorganize) during low-usage windows?
4. Are you on SQL Server 2019+ for batch mode on rowstore, or 2022+ for memory-optimized columnstore?

If you answered yes to the first three, create a CCI on a copy of the table in a test environment, run your top five analytical queries with SET STATISTICS IO, TIME ON, and compare plans. Look for Batch Mode and Columnstore Index Scan operators. If logical reads and elapsed time drop significantly, schedule the migration with a partition-switch strategy to minimize downtime.