What’s New for Columnstore Indexes in SQL Server 2025

---

Columnstore indexes have evolved significantly since their introduction in SQL Server 2012. SQL Server 2022 added ordered clustered columnstore indexes, which improved segment elimination for analytical queries. SQL Server 2025 extends that capability further with three targeted improvements: ordered non-clustered columnstore indexes, online rebuilds for ordered columnstore indexes, and more effective shrink operations when columns contain MAX data types.

Each improvement addresses a specific limitation that previously made columnstore less practical in mixed OLTP/analytics environments.

1 Ordered Non-Clustered Columnstore Indexes Intermediate

Ordered clustered columnstore indexes arrived in SQL Server 2022. The ORDER clause during creation allows the engine to store rowgroups in a specified key order, which dramatically improves segment elimination: SQL Server can skip entire rowgroups during a scan when the query filter matches the order column and the rowgroup metadata confirms no matching rows exist in that segment.

Before SQL Server 2025, this capability was limited to clustered columnstore indexes. Non-clustered columnstore indexes (NCCIs) always inherited the sort order of the underlying rowstore clustered index. If that ordering happened to match the reporting query patterns, segment elimination fired. If not, there was no way to specify an order that would help.

SQL Server 2025 extends the ORDER clause to non-clustered columnstore indexes. This means ordered segment elimination is now available on tables that still need a rowstore clustered index for OLTP operations, with a separate NCCI carrying the analytical order.

-- Unordered NCCI: no segment elimination on OrderDate filters
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID);

-- Ordered NCCI (SQL Server 2025): segment elimination fires on OrderDate filters
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID)
ORDER (OrderDate);

-- Check segment elimination in execution plan:
-- Look at the Columnstore Index Scan operator properties
-- "Segments Read" vs "Segments Skipped" confirms elimination is firing

Scenario	Rowgroups Scanned	Rowgroups Skipped	Result
Rowstore only (no columnstore)	All rows	0	Full table scan, high I/O
Unordered NCCI	16 rowgroups	0	Columnstore scan, no skipping
Ordered NCCI (SQL Server 2025)	11 rowgroups	5 (~5M rows skipped)	Segment elimination effective

The reduction in rowgroups scanned translates directly to fewer logical reads and lower CPU for date-range analytical queries on large fact tables. For real-time dashboards running against the same tables as OLTP transactions, ordered NCCIs are the most practical way to achieve segment elimination without restructuring the clustered index.

2 Testing Ordered NCCI with WideWorldImporters Advanced

The following setup creates a large test table from WideWorldImporters to demonstrate the before and after behavior. The INSERT statement uses a cross join to multiply rows to approximately 14.7 million, which is large enough to produce multiple rowgroups for meaningful segment elimination testing.

USE WideWorldImporters;
SET STATISTICS IO ON;

-- Create a large test table from Sales.Orders
SELECT TOP 0 *
INTO dbo.SalesOrdersBIG
FROM Sales.Orders;

ALTER TABLE dbo.SalesOrdersBIG
ADD CONSTRAINT PK_SalesOrdersBIG PRIMARY KEY CLUSTERED (OrderID ASC);

-- Populate with approximately 14.7 million rows
-- Adjust the column list to match Sales.Orders columns in your WideWorldImporters version
INSERT INTO dbo.SalesOrdersBIG
    (OrderID, CustomerID, SalespersonPersonID, PickedByPersonID,
     ContactPersonID, BackorderOrderID, OrderDate, ExpectedDeliveryDate,
     CustomerPurchaseOrderNumber, IsUndersupplyBackordered, Comments,
     DeliveryInstructions, InternalComments, PickingCompletedWhen,
     LastEditedBy, LastEditedWhen)
SELECT
    Orders.OrderID + (O2.OrderID * 100000),
    Orders.CustomerID,
    Orders.SalespersonPersonID,
    Orders.PickedByPersonID,
    Orders.ContactPersonID,
    Orders.BackorderOrderID,
    Orders.OrderDate,
    Orders.ExpectedDeliveryDate,
    Orders.CustomerPurchaseOrderNumber,
    Orders.IsUndersupplyBackordered,
    Orders.Comments,
    Orders.DeliveryInstructions,
    Orders.InternalComments,
    Orders.PickingCompletedWhen,
    Orders.LastEditedBy,
    Orders.LastEditedWhen
FROM Sales.Orders
LEFT JOIN Sales.Orders O2 ON O2.OrderID <= 200;

SELECT COUNT(*) AS RowCount FROM dbo.SalesOrdersBIG;
-- Expect approximately 14.7 million rows

Dashboard query to test with

-- Analytical dashboard query: aggregate by date with filter on one month
SELECT
    OrderDate,
    COUNT(*)                                AS OrderCount,
    AVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate)) AS AvgDeliveryHours,
    SUM(CASE WHEN BackorderOrderID IS NOT NULL THEN 1 ELSE 0 END) AS BackorderCount
FROM dbo.SalesOrdersBIG
WHERE OrderDate >= '2016-01-01'
  AND OrderDate  < '2016-02-01'
GROUP BY OrderDate
ORDER BY OrderDate;

-- Run this query three times:
-- 1. Against the rowstore table (no columnstore index)
-- 2. After creating an unordered NCCI
-- 3. After creating an ordered NCCI with ORDER (OrderDate)
-- Compare logical reads from SET STATISTICS IO ON output each time

3 Online Rebuilds for Ordered Columnstore Indexes Intermediate

When ordered columnstore indexes were introduced in SQL Server 2022, index rebuilds were offline operations. A rebuild blocked all queries and modifications to the table until it completed, which made scheduled maintenance windows mandatory. In a data warehouse with low overnight traffic this was acceptable. In a mixed OLTP/analytics system with 24-hour transactional workloads, it was a significant constraint.

SQL Server 2025 adds online rebuild support for both ordered clustered and non-clustered columnstore indexes in Enterprise Edition.

-- Offline rebuild (blocks readers and writers until complete)
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD;

-- Online rebuild (SQL Server 2025, Enterprise Edition)
-- Readers and writers continue during the rebuild
-- Schema stability and intent-shared locks only
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG
REBUILD WITH (ONLINE = ON);

4 Shrink Operations and MAX Data Types Beginner

A longstanding limitation of DBCC SHRINKDATABASE and DBCC SHRINKFILE was that they could not move LOB pages stored in columnstore indexes. Tables with VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) columns in a columnstore index held onto space that shrink operations could not reclaim, even after large volumes of data were deleted or archived.

SQL Server 2025 corrects this. Shrink operations can now move LOB pages from columnstore indexes, making space reclamation predictable and complete when large string or binary data has been removed from the table.

The environments that benefit most are those storing JSON or XML documents in NVARCHAR(MAX) columns, file or image content in VARBINARY(MAX) columns, and any fact table where periodic data archival removes large volumes of wide-column data.

-- After deleting or archiving columnstore data with MAX columns:
-- Check space used before shrink
EXEC sp_spaceused 'dbo.YourColumnstoreTable';

-- Shrink the database (SQL Server 2025: LOB pages from columnstore now included)
DBCC SHRINKDATABASE (YourDatabaseName);

-- Check space used after shrink to confirm reclamation
EXEC sp_spaceused 'dbo.YourColumnstoreTable';

5 Step-by-Step Lab Guide Advanced

This lab replicates the three SQL Server 2025 columnstore improvements using WideWorldImporters. Run on a non-production SQL Server 2025 instance.

-- Lab Step 1: Create the test table (see Section 2 for full INSERT)
USE WideWorldImporters;
SET STATISTICS IO ON;

-- Lab Step 2: Run the dashboard query on the raw rowstore table
-- Record logical reads from the Messages tab
SELECT OrderDate, COUNT(*), AVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate))
FROM dbo.SalesOrdersBIG
WHERE OrderDate >= '2016-01-01' AND OrderDate < '2016-02-01'
GROUP BY OrderDate ORDER BY OrderDate;

-- Lab Step 3: Create an unordered NCCI and rerun
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID);

-- Rerun the dashboard query and compare logical reads
-- Check execution plan: Columnstore Index Scan / Segments Read / Segments Skipped

-- Lab Step 4: Drop and recreate as ordered NCCI
DROP INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG;

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID)
ORDER (OrderDate);

-- Rerun dashboard query again, compare Segments Skipped in the execution plan

-- Lab Step 5: Test online rebuild
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG
REBUILD WITH (ONLINE = ON);
-- Run the dashboard query concurrently to confirm no blocking

-- Lab Step 6: Test shrink with MAX columns
ALTER TABLE dbo.SalesOrdersBIG ADD JsonPayload NVARCHAR(MAX);
UPDATE dbo.SalesOrdersBIG SET JsonPayload = REPLICATE(N'X', 1000)
WHERE OrderID % 10 = 0;   -- populate 10% of rows

EXEC sp_spaceused 'dbo.SalesOrdersBIG';   -- note space before delete

DELETE FROM dbo.SalesOrdersBIG WHERE OrderID % 10 = 0;

DBCC SHRINKDATABASE (WideWorldImporters);

EXEC sp_spaceused 'dbo.SalesOrdersBIG';   -- compare space after shrink

-- Lab Step 7: Clean up
DROP TABLE dbo.SalesOrdersBIG;

References

• Microsoft Docs: Columnstore Indexes Overview
• Microsoft Docs: Columnstore Indexes Design Guidance
• Microsoft Docs: Reorganize and Rebuild Indexes
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: SQL Server Compression and Partitioning Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide