What’s New for Columnstore Indexes in SQL Server 2025

Columnstore indexes have come a long way since they were first introduced in SQL Server 2012. Every release since then has added something to make them faster, more flexible, or easier to maintain. With SQL Server 2025, Microsoft has made another set of improvements — this time targeting performance and business continuity.

Three key areas have changed:

• Ordered non-clustered columnstore indexes
• Online rebuilds for ordered columnstore indexes
• More effective shrink operations with MAX data types

This post walks through each improvement, why it matters, and how you can test it yourself.

---

Ordered Non-Clustered Columnstore Indexes

Ordered clustered columnstore indexes arrived in SQL Server 2022, letting DBAs specify an ORDER clause during creation. That ordering improved both compression and segment elimination (the ability for SQL Server to skip over rowgroups that don’t match your query filter).

SQL Server 2025 extends that option to non-clustered columnstore indexes (NCCIs). Before this change, NCCIs always inherited the sort order of the underlying clustered rowstore index. If that happened to line up with your reporting needs, you were in luck. If not, there wasn’t much you could do.

Demo Setup

Here’s a way to test the feature using the WideWorldImporters database. First, generate a large table with a rowstore clustered primary key:

USE WideWorldImporters;
SET STATISTICS IO ON;

SELECT TOP 0 *
INTO dbo.SalesOrdersBIG
FROM Sales.Orders;

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

INSERT INTO dbo.SalesOrdersBIG (...)
SELECT Orders.OrderID + (O2.OrderID * 100000), ...
FROM Sales.Orders
LEFT JOIN Sales.Orders O2 ON O2.OrderID <= 200;

This produces about 14.7 million rows — plenty for testing columnstore behavior.

Analytic Query Without Columnstore

Imagine a dashboard query like this:

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;

On a plain rowstore table, this query performs a full table scan — heavy I/O and contention with OLTP transactions.

Adding a Non-Clustered Columnstore

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

This improves performance, but because the index isn’t ordered, all rowgroups are still scanned.

Ordered NCCI in Action

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

Now, SQL Server skips several rowgroups when running the dashboard query — reducing logical reads by about one-third.

Before vs. After Comparison

Scenario	Rowgroups Scanned	Rowgroups Skipped	Logical Reads	Notes
No Columnstore	All rows	0	Very high	Rowstore scan only
Unordered NCCI	16	0	Moderate	Columnstore scan, no skipping
Ordered NCCI	11	5 (~5M rows)	~⅓ lower	Segment elimination effective

For real-time dashboards and operational analytics, ordered NCCIs are a major win.

---

Online Rebuilds for Ordered Columnstore Indexes

When ordered columnstore indexes were first introduced, their rebuilds were offline. That meant queries and updates were blocked until the rebuild finished — not a dealbreaker in a warehouse, but a big problem for mixed OLTP/analytics systems.

SQL Server 2025 introduces online rebuilds for both clustered and non-clustered ordered columnstore indexes (Enterprise Edition only).

Example

Offline rebuild:

ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD;

ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD;

This blocks readers and writers.

Online rebuild:

ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG
REBUILD WITH (ONLINE = ON);

Now the rebuild runs without blocking queries. Locks are light (schema stability and intent-shared), and the index remains available.

👉 Tip: Don’t rebuild too often. Use ALTER INDEX … REORGANIZE as your standard maintenance, and reserve rebuilds for when data order is badly fragmented.

---

Shrink Operations and MAX Data Types

For years, DBAs were frustrated that DBCC SHRINKDATABASE and DBCC SHRINKFILE didn’t free up space from LOB pages in columnstore indexes containing:

• VARCHAR(MAX)
• NVARCHAR(MAX)
• VARBINARY(MAX)

That meant databases often stayed larger than expected even after purging data.

SQL Server 2025 changes this. Shrink operations can now move those pages, making space reclamation far more predictable when large string or binary data is involved.

This is especially helpful for environments that:

• Store JSON or XML documents in NVARCHAR(MAX)
• Keep file or image data in VARBINARY(MAX)
• Regularly archive or delete large volumes of records

Not every columnstore table will benefit, but when you’re dealing with wide or string-heavy fact tables, the improvement is significant.

---

Key Takeaways

SQL Server 2025 pushes columnstore further into real-time analytics scenarios:

• Ordered NCCIs make segment elimination possible without restructuring tables.
• Online rebuilds allow maintenance without downtime (Enterprise Edition).
• Smarter shrink helps reclaim space with MAX data types in columnstore.

If you’re running SQL Server and haven’t yet tested columnstore, now is the time. Start with reporting or dashboard queries, compare performance across rowstore, unordered NCCI, and ordered NCCI, and measure the gains.

For more details, check Microsoft’s official docs:

• Columnstore indexes overview
• Create columnstore indexes with ORDER
• Index maintenance: reorganize vs rebuild

---

Step-by-Step Lab Guide

If you’d like to replicate the examples in this post, here’s a script sequence you can follow:

1. Setup
   • Install the WideWorldImporters sample database.
   • Connect with SQL Server Management Studio (SSMS).
2. Create Test Table

USE WideWorldImporters;
SELECT TOP 0 * INTO dbo.SalesOrdersBIG FROM Sales.Orders;
ALTER TABLE dbo.SalesOrdersBIG
ADD CONSTRAINT PK_SalesOrdersBIG PRIMARY KEY CLUSTERED (OrderID ASC);
-- Populate with ~14M rows
INSERT INTO dbo.SalesOrdersBIG (...)
SELECT Orders.OrderID + (O2.OrderID * 100000), ...
FROM Sales.Orders
LEFT JOIN Sales.Orders O2 ON O2.OrderID <= 200;

3. Run Dashboard Query on Rowstore

• Use the query provided in the blog.
• Note I/O cost (SET STATISTICS IO ON).

Create an Unordered NCCI

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

• Rerun the query again, compare reads.
• Look at segment elimination in execution plan properties.

6. Test Online Rebuild

ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG
REBUILD WITH (ONLINE = ON);

• Run queries concurrently to confirm no blocking.

7. Test Shrink with MAX Columns

• Add a VARCHAR(MAX) column to your table and populate with dummy data.
• Delete some rows and run:

DBCC SHRINKDATABASE (WideWorldImporters);

• Check space reclaimed using sp_spaceused.

✅ With these steps, you’ll see exactly how SQL Server 2025 improves ordered columnstore indexes in practice.