Columnstore Indexes

38.1 Concepts

Data is stored by column in rowgroups (~1,048,576 rows) made of compressed segments.

Deltastore (rowstore) receives trickle inserts. The tuple mover compresses closed rowgroups.

Batch mode processing handles ~900 rows at a time. Segment elimination skips segments using min/max metadata.

Typically 10x compression and 10–100x faster analytics.

38.2 Types

-- Clustered columnstore (the whole table; data warehouse fact tables)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

-- Ordered CCI (2022+) for better segment elimination
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales ORDER (SaleDate);

-- Nonclustered columnstore on an OLTP table (real-time operational analytics)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders ON dbo.Orders (OrderDate, CustomerID, Status)
WHERE Status = 'Delivered';   -- filtered

38.3 Maintenance and Monitoring

Best practices: bulk load in batches of ≥ 102,400 rows to go straight to compressed rowgroups, and avoid heavy singleton updates.

SELECT * FROM sys.dm_db_column_store_row_group_physical_stats;
ALTER INDEX CCI_FactSales ON dbo.FactSales REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);