Clustered vs Nonclustered Columnstore, Delta Stores, Tuple Mover, and Maintenance

Learning outcomes

ServiceHub needs two analytical shapes. Historical facts are scan-heavy and naturally fit clustered columnstore. The live work-order table still needs fast row-oriented inserts, primary-key seeks and OLTP updates, but managers want near-real-time aggregates over it. SQL Server therefore offers both clustered columnstore storage and nonclustered columnstore indexes (NCCI) over rowstore. Choosing between them requires understanding delta stores, tuple mover, delete handling, compression delay, ordered columnstore and the maintenance cost of keeping two physical representations current.

1. CCI changes primary storage; NCCI adds an analytical copy

A CCI replaces heap/clustered-B-tree storage for the table. Additional B-tree nonclustered indexes can still exist, but the table's main representation is columnstore. An NCCI does the opposite: the base table remains a heap or B-tree, while selected columns are copied into columnstore format for analytical access. That second representation is why NCCI is a common Hybrid Transactional/Analytical Processing (HTAP) pattern: OLTP keeps its rowstore keys and narrow updates, while analytics can scan the columnstore copy.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab19.LiveWorkOrder;GOCREATE TABLE lab19.LiveWorkOrder(  work_order_id bigint IDENTITY(2000000,1) NOT NULL PRIMARY KEY,  opened_at datetime2(0) NOT NULL,  region_code char(3) NOT NULL,  status varchar(16) NOT NULL,  priority tinyint NOT NULL,  labor_minutes int NOT NULL,  amount decimal(12,2) NOT NULL,  description nvarchar(200) NULL);GO;WITH n AS(  SELECT TOP (140000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab19.LiveWorkOrder(opened_at,region_code,status,priority,labor_minutes,amount,description)SELECT DATEADD(second,CONVERT(int,n),'2026-01-01'),       CASE n%4 WHEN 0 THEN 'N01' WHEN 1 THEN 'S02' WHEN 2 THEN 'E03' ELSE 'W04' END,       CASE n%4 WHEN 0 THEN 'NEW' WHEN 1 THEN 'ASSIGNED' WHEN 2 THEN 'ONSITE' ELSE 'CLOSED' END,       CONVERT(tinyint,1+n%5),       10+CONVERT(int,n%300),       CONVERT(decimal(12,2),25+(n%5000)/10.0),       N'Operational analytics sample'FROM n;GOCREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_LiveWorkOrder_AnalyticsON lab19.LiveWorkOrder(opened_at,region_code,status,priority,labor_minutes,amount)WITH (COMPRESSION_DELAY = 10);GO

The rowstore primary key remains the OLTP access path. The NCCI deliberately excludes the large description column because the analytics shown here do not need it. COMPRESSION_DELAY can keep recently closed delta rowgroups uncompressed for a configured period, which can reduce repeated compression churn when rows are still likely to be updated. It is a workload knob, not a default value to copy blindly.

2. Delta store and tuple mover explain why recent data may not be compressed

Small/trickle inserts first enter an OPEN delta rowgroup, a B-tree rowstore structure associated with columnstore. At the rowgroup size threshold it becomes CLOSED. The tuple mover wakes periodically and compresses eligible closed groups. SQL Server 2019 and later also has a background merge task that can combine smaller compressed groups or compress smaller delta groups after internal thresholds. The system therefore evolves without a nightly “compress everything” job, although explicit maintenance can still be justified.

;WITH n AS(  SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects)INSERT lab19.LiveWorkOrder(opened_at,region_code,status,priority,labor_minutes,amount,description)SELECT DATEADD(second,CONVERT(int,n),'2026-06-01'),       'N01','NEW',2,30,125.00,N'Recent write'FROM n;GOSELECT row_group_id,state_desc,total_rows,deleted_rows,       trim_reason_desc,transition_to_compressed_state_descFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LiveWorkOrder')  AND index_id=INDEXPROPERTY(OBJECT_ID(N'lab19.LiveWorkOrder'),N'NCCI_LiveWorkOrder_Analytics','IndexID')ORDER BY row_group_id;GO

An OPEN group after the insert is expected and is not fragmentation in the rowstore sense. If the workload finishes a batch and you know those rows are unlikely to change, REORGANIZE ... COMPRESS_ALL_ROW_GROUPS = ON can force open/closed delta groups into compressed columnstore. That operation consumes CPU, I/O and log; it should be driven by a load boundary and measured query need.

ALTER INDEX NCCI_LiveWorkOrder_AnalyticsON lab19.LiveWorkOrderREORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);GOSELECT state_desc,COUNT(*) AS rowgroups,SUM(total_rows) AS rows,SUM(deleted_rows) AS deleted_rowsFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LiveWorkOrder')GROUP BY state_desc;GO

3. SQL Server 2025 ordered NCCI targets real-time operational analytics

SQL Server 2022 introduced ordered clustered columnstore. SQL Server 2025 extends ordering to nonclustered columnstore, so an operational rowstore can keep its transactional layout while the analytical NCCI reduces segment overlap for frequently filtered columns. The ORDER clause controls segment ordering metadata; it does not turn columnstore into a B-tree that guarantees row-return order. Queries still need ORDER BY for deterministic presentation.

DROP INDEX IF EXISTS NCCI_LiveWorkOrder_Analytics ON lab19.LiveWorkOrder;GOCREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_LiveWorkOrder_AnalyticsON lab19.LiveWorkOrder(opened_at,region_code,status,priority,labor_minutes,amount)ORDER (opened_at, region_code)WITH (MAXDOP = 1, COMPRESSION_DELAY = 10);GOSELECT c.name AS column_name,       ic.column_store_order_ordinalFROM sys.index_columns AS icJOIN sys.columns AS c  ON c.object_id=ic.object_id AND c.column_id=ic.column_idWHERE ic.object_id=OBJECT_ID(N'lab19.LiveWorkOrder')  AND ic.index_id=INDEXPROPERTY(OBJECT_ID(N'lab19.LiveWorkOrder'),N'NCCI_LiveWorkOrder_Analytics','IndexID')ORDER BY ic.column_store_order_ordinal,c.column_id;GO

MAXDOP = 1 generally improves ordering quality but can lengthen index build time. Parallel builders sort thread-local subsets, so segment ranges can overlap more. SQL Server 2025 can create/rebuild ordered clustered or nonclustered columnstore online, and an online ordered build with MAXDOP=1 can use tempdb to achieve full ordering. However, the SQL Server 2025 edition matrix still makes online index create/rebuild an Enterprise feature; do not deploy ONLINE=ON into Standard/Express maintenance scripts merely because the syntax exists.

-- Optional: Enterprise / Enterprise Developer only on boxed SQL Server 2025.-- Size tempdb for the build and test duration/logging before production use.CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_LiveWorkOrder_AnalyticsON lab19.LiveWorkOrder(opened_at,region_code,status,priority,labor_minutes,amount)ORDER (opened_at,region_code)WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);GO

4. Deletes and updates create maintenance work; they do not rewrite segments in place

For compressed columnstore rows, deletes mark rows logically deleted. Updates behave as delete-plus-insert. A high-churn table can therefore accumulate deleted-row pressure and delta-store activity even when its rowstore side remains healthy. For an NCCI, deleted_rows in the rowgroup DMV does not include every delete-buffer detail, so diagnose the full structure rather than copying a single percentage threshold.

UPDATE lab19.LiveWorkOrderSET status='CLOSED', labor_minutes=labor_minutes+5WHERE work_order_id % 17 = 0;DELETE lab19.LiveWorkOrderWHERE work_order_id % 113 = 0;GOSELECT row_group_id,state_desc,total_rows,deleted_rows,       CAST(100.0*deleted_rows/NULLIF(total_rows,0) AS decimal(6,2)) AS deleted_pct,       trim_reason_descFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LiveWorkOrder')ORDER BY row_group_id;GO

5. Production judgment and bridge

CCI is appropriate when analytics is the dominant storage purpose. NCCI is appropriate when the transactional rowstore must remain primary but analytical scans justify a maintained columnstore copy. Ordered columnstore is useful when frequently filtered columns benefit from narrower segment ranges and the extra sort/build cost is acceptable. Monitor rowgroup states, delete pressure, ordered-column metadata, maintenance duration, log/tempdb impact and OLTP write cost. Lesson 3 shifts from physical organization to execution: batch mode, pushdown and plan evidence.

Authoritative references

• What's new in columnstore indexes
• Performance tuning with ordered columnstore indexes
• Columnstore indexes - Data loading guidance
• CREATE COLUMNSTORE INDEX
• SQL Server 2025 editions and supported features