{"id":107195,"date":"2025-06-30T14:00:00","date_gmt":"2025-06-30T14:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107195"},"modified":"2026-04-08T10:27:06","modified_gmt":"2026-04-08T10:27:06","slug":"columnstore-index-improvements-in-sql-server-2025","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/columnstore-index-improvements-in-sql-server-2025\/","title":{"rendered":"Columnstore Index Improvements in SQL Server 2025\u00a0"},"content":{"rendered":"\n

Columnstore indexes<\/a> are a powerful tool for storing analytic data directly in SQL Server<\/a>. This feature has improved in every version of SQL Server since their inception over ten years ago, and SQL Server 2025<\/a> is no exception! <\/p>\n\n\n\n

The newest enhancements are laser-focused on business continuity and performance. Ordered clustered columnstore indexes, ordered non-clustered columnstore indexes, and database\/file shrink operations are all given significant boosts that are worth the time to introduce and learn. <\/p>\n\n\n\n

In this article we will dive into each of these changes, how they impact columnstore workloads in SQL Server, and demonstrate their operation. <\/p>\n\n\n\n

Ordered Non-Clustered Columnstore Indexes <\/h3>\n\n\n\n

Ordered clustered columnstore indexes were introduced in SQL Server 2022<\/a> and provided an optional ORDER clause to the index creation statement, allowing data to be pre-sorted on a key column or set of key columns. Sorted data allows for better segment elimination and can improve performance and compression on the table. <\/p>\n\n\n\n

SQL Server 2025 adds the same syntax to non-clustered columnstore indexes. This use-case is particularly interesting as prior to this feature, a non-clustered columnstore index would inherit its order from the clustered rowstore index. If analytics needs aligned with the clustered rowstore order, then performance would be quite good. If not, then there was little that could be done without creating new tables or re-architecting the existing one. <\/p>\n\n\n\n

To test this feature, a large rowstore table will be created: <\/p>\n\n\n\n

USE WideWorldImporters; \n\nSET STATISTICS IO ON; \n\n \n\nSELECT TOP 0 \n\n* \n\nINTO dbo.SalesOrdersBIG \n\nFROM Sales.Orders; \n\n \n\nALTER TABLE dbo.SalesOrdersBIG ADD CONSTRAINT PK_SalesOrdersBIG PRIMARY KEY CLUSTERED  \n\n(OrderID ASC); \n\nGO \n\n \n\nINSERT INTO dbo.SalesOrdersBIG \n\n(OrderID, CustomerID, SalespersonPersonID, PickedByPersonID, ContactPersonID, BackorderOrderID, OrderDate, ExpectedDeliveryDate, \n\n     CustomerPurchaseOrderNumber, IsUndersupplyBackordered, Comments, DeliveryInstructions, InternalComments, PickingCompletedWhen, \n\n     LastEditedBy, LastEditedWhen) \n\nSELECT \n\n Orders.OrderID + (ORDERS2.OrderID * 100000) AS OrderID, \n\n Orders.CustomerID, Orders.SalespersonPersonID, Orders.PickedByPersonID, Orders.ContactPersonID, Orders.BackorderOrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate, \n\n     Orders.CustomerPurchaseOrderNumber, Orders.IsUndersupplyBackordered, Orders.Comments, Orders.DeliveryInstructions, Orders.InternalComments, Orders.PickingCompletedWhen, \n\n     Orders.LastEditedBy, Orders.LastEditedWhen \n\nFROM Sales.Orders \n\nLEFT JOIN Sales.Orders ORDERS2 \n\nON ORDERS2.OrderID <= 200;<\/pre><\/div>\n\n\n\n
This T-SQL<\/a> creates a table with 14.72 million rows in it. Not a huge amount, but enough to test columnstore index queries, performance, and metadata with meaningful results. <\/p>\n\n\n\n
The realistic use-case for a non-clustered columnstore index on this data would be a need for real-time operational analytics that filter primarily on OrderDate<\/em>. Whether a quarterly sales report or a live sales dashboard, there are many ways this data can be sliced and diced. Consider this analytic query used to power a daily sales dashboard that includes current data, in addition to historical data: <\/p>\n\n\n\n
SELECT \n\nOrderDate, \n\nCOUNT(*) AS OrderCount, \n\nAVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate)) AS AvgOrderDeliveryTimeHours, \n\nSUM(CASE WHEN BackorderOrderID IS NOT NULL THEN 1 ELSE 0 END) AS BackorderCount \n\nFROM dbo.SalesOrdersBIG \n\nWHERE OrderDate >= '1\/1\/2016' \n\nAND OrderDate < '2\/1\/2016' \n\nGROUP BY OrderDate \n\nORDER BY OrderDate;<\/pre><\/div>\n\n\n\n
The results show a day-by-day analysis of a few sales-related metrics: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
A quick check of STATISTICS IO and the execution shows that this query is reading a LOT of data: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Running this frequently against a large transactional table ultimately causes conflict with users that are trying to place orders, update them, or check on their status. In addition, if this server is not running Enterprise edition, then batch mode will not be used when processing this data. To alleviate this pressure, a non-clustered columnstore index is created on the table: <\/p>\n\n\n\n
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG \n\nON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID);<\/pre><\/div>\n\n\n\n
Once created, performance improves greatly and the execution plan now reflects a columnstore index scan and significantly fewer reads: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The improvement is noticeably better, but when I look closely, I notice that all 16 rowgroups in the columnstore index were read. This is denoted in STATISTICS IO via the segment reads, which show that zero were skipped. This indicates that no segment elimination occurred when executing the query. <\/p>\n\n\n\n
The ideal way to improve segment elimination and reduce the number of rowgroups that need to be read is by controlling data order. If this were a clustered columnstore index, the solution would be to rebuild the table with a rowstore index and swap it with a clustered columnstore index. Alternatively, an ordered clustered columnstore index could be used in SQL Server 2022 and higher. Since this is a clustered rowstore table, data order is directly inherited from that index into the non-clustered columnstore index. <\/p>\n\n\n\n
To resolve this, an ordered non-clustered columnstore index will be created:<\/p>\n\n\n\n
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG \n\nON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID) \n\nORDER (OrderDate);<\/pre><\/div>\n\n\n\n
This index will order the columnstore index rows by OrderDate<\/em> prior to creating rowgroups. Once complete, the analytic query from earlier can be run again and its STATISTICS IO inspected: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Five of 11 rowgroups were skipped and logical reads were reduced by about 1\/3. The execution plan did not change as a part of this. The change was that the filter predicate on OrderDate<\/em> was used to successfully skip 5 rowgroups worth of data, which equates to about 5 million rows or about 1\/3 of the table. <\/p>\n\n\n\n
For real-time operational analytics where performance is critical, using an ordered non-clustered columnstore index can allow for segment elimination where normally it would not be possible. This provides additional performance gains over an unordered non-clustered columnstore index and comparatively massive gains when compared to querying a rowstore table directly. <\/p>\n\n\n\n
Online Index Rebuilds for Ordered Columnstore Indexes <\/h3>\n\n\n\n
One of the significant drawbacks to ordered columnstore indexes when they were first released was that index rebuilds<\/a> were an offline operation. While this did operate on a partition-by-partition basis, it meant that rebuilds would make data unavailable for the duration of the index maintenance operation. <\/p>\n\n\n\n
For clustered columnstore indexes, this was typically a bit more tolerable as maintenance windows are easier to secure for analytic data than transactional data. For the newly-introduced ordered non-clustered columnstore indexes, though, taking data offline while rebuilding is far less acceptable. <\/p>\n\n\n\n
SQL Server 2025 supports online index rebuilds for both clustered and non-clustered ordered columnstore indexes. This is a game-changer for this feature as it ensures business continuity when ordered columnstore indexes are created or rebuilt. Note that online rebuilds are an enterprise-only feature. If a server is not running SQL Server 2025+ enterprise edition, then this improvement will not be relevant. This change does not introduce online index rebuilds to standard, web, or other editions of SQL Server. Developer edition, being all-inclusive, does feature online rebuilds for any indexes that support them, which now includes ordered columnstore indexes. <\/p>\n\n\n\n
Consider the ordered non-clustered columnstore index that was created in the last demonstration: <\/p>\n\n\n\n
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG \n\nON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID) \n\nORDER (OrderDate);<\/pre><\/div>\n\n\n\n
A rebuild of this index will lock it for the entirety of its runtime. Users trying to read or write data will be left waiting, which is not desirable for a typical transactional application. Here is the T-SQL to rebuild that index: <\/p>\n\n\n\n
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD;<\/pre><\/div>\n\n\n\n
In a separate query, we will check dm_tran_locks<\/em>  for locks against the table while the index creation statement is running. While the index creation is running, the following are the results: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Ah, well, that is awkward. The columnstore index is locked, and so the system view dm_tran_locks<\/em> cannot return any data. Instead, the analytic query from earlier will be used to test it: <\/p>\n\n\n\n
SELECT \n\nOrderDate, \n\nCOUNT(*) AS OrderCount, \n\nAVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate)) AS AvgOrderDeliveryTimeHours, \n\nSUM(CASE WHEN BackorderOrderID IS NOT NULL THEN 1 ELSE 0 END) AS BackorderCount \n\nFROM dbo.SalesOrdersBIG \n\nWHERE OrderDate >= '1\/1\/2016' \n\nAND OrderDate < '2\/1\/2016' \n\nGROUP BY OrderDate \n\nORDER BY OrderDate;<\/pre><\/div>\n\n\n\n
As expected, results are not returned as the query as I was waiting for the rebuild to complete: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
As soon as the index rebuild is complete, the results are returned as expected: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
A small adjustment to the index rebuild statement allows it to be executed online, without needing to block users trying to access the table: <\/p>\n\n\n\n
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD \n\nWITH (ONLINE = ON);<\/pre><\/div>\n\n\n\n
When the analytic query from earlier is executed, results are immediately returned. Similarly, the query against dm_tran_locks<\/em> not only executes immediately without waiting, but also returns results: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The locks taken during the index rebuild are all schema and intent-shared locks, which ensure transactional integrity for the index being rebuilt, but do not block users from accessing data in the table. Finally, a check of the execution plan for the analytic query confirms that the non-clustered columnstore index was used to return its results: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
This confirms that the analytic query was using the index as it was being rebuilt online and was not using the rowstore index instead. <\/p>\n\n\n\n
For SQL Servers running enterprise edition, the ability to rebuild ordered columnstore indexes as an online operation greatly improves the usability of this feature. Index maintenance can run on columnstore indexes without fear of blocking users trying to access real-time operational analytics. <\/p>\n\n\n\n
One final (and critical) note on columnstore indexes: rebuild operations are not often needed. The reorganize operation performs a wide variety of useful tasks on columnstore indexes, including: <\/p>\n\n\n
\n