Columnstore Indexes in SQL Server

• ROWSTORE (normal tables): data stored ROW by row in pages. Great for OLTP: "get / update order 123".
• COLUMNSTORE: data stored COLUMN by column, in compressed "segments" of up to ~1 million rows (row groups). Great for ANALYTICS: "SUM(Amount) by Region for 2024".

Rowstore page : [1,Hyd,500][2,Pune,300][3,Hyd,900] ...

Columnstore   :  ID:    [1,2,3 ...]
                 City:  [Hyd,Pune,Hyd ...]   -> compresses very well (repeated values)
                 Amount:[500,300,900 ...]

Why fast for analytics?
  - Reads ONLY the columns used in the query
  - High compression (often 5x - 10x) -> less I/O
  - Segment elimination (skips row groups using min/max values)
  - BATCH MODE execution (processes ~900 rows at a time)

Types:

CLUSTERED COLUMNSTORE INDEX (CCI)     : the whole table is columnstore (data warehouse fact tables)
NONCLUSTERED COLUMNSTORE INDEX (NCCI) : extra columnstore copy on a rowstore table
                                        (real-time analytics on OLTP = HTAP)

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

---------------------------------------------------------------------
-- Lesson tables : a rowstore fact table and a columnstore fact table
-- To get more realistic sizes we load the 300k rows 4 times = 1.2 million rows
---------------------------------------------------------------------
DROP TABLE IF EXISTS dbo.FactSalesRow;
DROP TABLE IF EXISTS dbo.FactSalesCCI;
GO

CREATE TABLE dbo.FactSalesRow
(
    SalesKey    INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    OrderID     INT           NOT NULL,
    CustomerID  INT           NOT NULL,
    ProductID   INT           NOT NULL,
    OrderDate   DATE          NOT NULL,
    Region      VARCHAR(10)   NOT NULL,
    Status      VARCHAR(10)   NOT NULL,
    Quantity    INT           NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL
);

INSERT INTO dbo.FactSalesRow WITH (TABLOCK) (OrderID, CustomerID, ProductID, OrderDate, Region, Status, Quantity, Amount)
SELECT o.OrderID, o.CustomerID, o.ProductID,
       DATEADD(YEAR, -c.x, o.OrderDate),          -- shift years so copies are different
       o.Region, o.Status, o.Quantity, o.Amount
FROM dbo.BigOrdersData o
CROSS JOIN (VALUES (0), (1), (2), (3)) AS c(x)
ORDER BY DATEADD(YEAR, -c.x, o.OrderDate);        -- load in date order (helps segment elimination later)
GO

-- Same data as a CLUSTERED COLUMNSTORE table
CREATE TABLE dbo.FactSalesCCI
(
    SalesKey    INT           NOT NULL,
    OrderID     INT           NOT NULL,
    CustomerID  INT           NOT NULL,
    ProductID   INT           NOT NULL,
    OrderDate   DATE          NOT NULL,
    Region      VARCHAR(10)   NOT NULL,
    Status      VARCHAR(10)   NOT NULL,
    Quantity    INT           NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL,
    INDEX CCI_FactSalesCCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.FactSalesCCI WITH (TABLOCK)
SELECT SalesKey, OrderID, CustomerID, ProductID, OrderDate, Region, Status, Quantity, Amount
FROM dbo.FactSalesRow
ORDER BY OrderDate;
GO

-- Compress any open row groups so the demo shows final state
ALTER INDEX CCI_FactSalesCCI ON dbo.FactSalesCCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

(1200 rows affected)
(1200 rows affected)

Part A: Storage Size Comparison

EXEC sp_spaceused 'dbo.FactSalesRow';
EXEC sp_spaceused 'dbo.FactSalesCCI';      -- typically many times smaller
GO

Part B: Analytic Query Performance

Compare "logical reads" (rowstore) with "lob logical reads" (columnstore) and look for "Columnstore Index Scan" + "Batch" execution mode in the plan.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Revenue by Region and Year

SELECT Region, YEAR(OrderDate) AS SalesYear, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM dbo.FactSalesRow
GROUP BY Region, YEAR(OrderDate)
ORDER BY Region, SalesYear;

SELECT Region, YEAR(OrderDate) AS SalesYear, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM dbo.FactSalesCCI
GROUP BY Region, YEAR(OrderDate)
ORDER BY Region, SalesYear;

SQL Server parse and compile time: 
   CPU time = 20 ms, elapsed time = 20 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'FactSalesRow'. Scan count 1, logical reads 14, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Table 'FactSalesCCI'. Scan count 1, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'FactSalesCCI'. Segment reads 1, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Top products in one year (segment elimination on OrderDate - data loaded in date order)

SELECT TOP (5) ProductID, SUM(Quantity) AS Units, SUM(Amount) AS Revenue
FROM dbo.FactSalesCCI
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
GROUP BY ProductID
ORDER BY Revenue DESC;
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Table 'FactSalesCCI'. Scan count 1, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 17, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'FactSalesCCI'. Segment reads 1, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Part C: Inside the Columnstore (row groups and segments)

Row groups: state should be Compressed, up to 1,048,576 rows each

SELECT row_group_id, state_desc, total_rows, deleted_rows, size_in_bytes / 1024 AS SizeKB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSalesCCI')
ORDER BY row_group_id;

Warning: The join order has been enforced because a local join hint is used.

Segments: one per column per row group, with MIN/MAX (used for segment elimination)

SELECT c.name AS ColumnName, s.segment_id, s.row_count, s.on_disk_size / 1024 AS SizeKB,
       s.min_data_id, s.max_data_id
FROM sys.column_store_segments s
JOIN sys.partitions p ON p.hobt_id = s.hobt_id
JOIN sys.columns c    ON c.object_id = p.object_id AND c.column_id = s.column_id
WHERE p.object_id = OBJECT_ID('dbo.FactSalesCCI')
ORDER BY c.column_id, s.segment_id;
GO

Part D: DML ON Columnstore (deltastore and delete bitmap)

• Small inserts (< 102,400 rows) go to a rowstore "DELTASTORE" (OPEN row group).
• Bulk inserts >= 102,400 rows go straight into compressed row groups.
• DELETE only marks rows as deleted; UPDATE = delete + insert.
• REORGANIZE compresses delta stores and cleans deleted rows.

INSERT INTO dbo.FactSalesCCI (SalesKey, OrderID, CustomerID, ProductID, OrderDate, Region, Status, Quantity, Amount)
VALUES (9999999, 9999999, 1, 1, '2025-12-31', 'North', 'Pending', 1, 999.00);

DELETE FROM dbo.FactSalesCCI WHERE OrderDate < '2019-02-01';

SELECT row_group_id, state_desc, total_rows, deleted_rows
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSalesCCI')
ORDER BY row_group_id;          -- see an OPEN delta row group and deleted_rows > 0

ALTER INDEX CCI_FactSalesCCI ON dbo.FactSalesCCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

Warning: The join order has been enforced because a local join hint is used.
(1 row affected)
(7 rows affected)

Part E: Nonclustered Columnstore on an OLTP (rowstore) table

Keep fast single-row lookups (rowstore PK) AND fast analytics (NCCI).

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FactSalesRow
    ON dbo.FactSalesRow (OrderDate, Region, Status, ProductID, Quantity, Amount);
GO

SET STATISTICS IO ON;
-- Analytic query now uses the NCCI automatically
SELECT Status, SUM(Amount) AS Revenue FROM dbo.FactSalesRow GROUP BY Status;
-- Point lookup still uses the clustered rowstore index (seek)
SELECT * FROM dbo.FactSalesRow WHERE SalesKey = 500;
SET STATISTICS IO OFF;
GO

Table 'FactSalesRow'. Scan count 2, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'FactSalesRow'. Segment reads 1, segment skipped 0.

Table 'FactSalesRow'. Scan count 0, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Filtered NCCI: analytics only on "cold" (older) data, hot data stays rowstore-only

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Cold ON dbo.FactSalesRow (OrderDate, Amount) WHERE OrderDate < '2025-01-01';

GO

When to Use What

PRACTICE QUESTIONS 1. Compare reads for: SELECT AVG(Quantity) FROM each fact table WHERE Region = 'West'. 2. Load the data into FactSalesCCI in RANDOM order (ORDER BY NEWID()) and compare segment elimination (min/max per segment) for a date filter. 3. Insert 1,000 single rows into FactSalesCCI and look at the row group states. Then REORGANIZE. 4. Build a CCI table for dbo.Sales (from the core data) and write a monthly revenue report on it.