Ordered columnstore indexes (SQL Server 2022 and 2025)

In one line: an ordered columnstore index lets a range query skip rowgroups, but only as far as the rows are still sorted. A parallel build, and any rows loaded after it, put the overlap back until the next REBUILD.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/ordered-columnstore on GitHub.

What you’ll see

  • “Segment reads 5, segment skipped 0” in SET STATISTICS IO for a query on one month, on a columnstore table holding four years.
  • A query that was fast after the index build and slower after the next load: one segment read became seven.
  • On SQL Server 2022, error 35342 for ORDER on a nonclustered columnstore index, and error 10679 for an ordered index built with ONLINE = ON.

Why it happens

Every rowgroup stores each column’s minimum and maximum. A query skips a rowgroup when its range falls outside them. Rows loaded in no particular order put every date in every rowgroup, so nothing can be skipped.

ORDER (column) sorts the rows when the index is built or rebuilt. Built in parallel, each thread sorts its own share, and the rowgroups’ ranges overlap. Rows added later go into new rowgroups in arrival order; they aren’t merged into the sorted ones.

What to do

  • Order by the column your range queries filter on, usually a date.
  • Build with MAXDOP = 1. In the demo, MAXDOP 4 read four or five segments where MAXDOP 1 read one.
  • REBUILD after big loads, with MAXDOP = 1. On 2025 it can run with ONLINE = ON.
  • On 2025, consider an ordered nonclustered columnstore index for analytics on a rowstore table.

What doesn’t help

  • A parallel build to save time. The rowgroups overlap and more of them are read.
  • Ordering by a column the queries don’t filter on.

Check your server

-- Columnstore indexes in this database, and the column each is ordered by
-- (none for an unordered index). SQL Server 2022+.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.name                          AS index_name,
       i.type_desc,
       COL_NAME(ic.object_id, ic.column_id) AS order_column
FROM sys.indexes AS i
LEFT JOIN sys.index_columns AS ic
       ON ic.object_id = i.object_id AND ic.index_id = i.index_id
      AND ic.column_store_order_ordinal = 1
WHERE i.type IN (5, 6)
ORDER BY schema_name, table_name;

See it happen

The ordered-columnstore demo builds the same 4,000,000 rows unordered, ordered at MAXDOP 1 and at MAXDOP 4, loads another 1,048,576 rows, and rebuilds. It counts the segments one month’s query reads each time. On 2025 it also builds an ordered nonclustered index and an ordered index online; on 2022 it shows the errors.

Related: columnstore and the delta store · data compression · partition elimination

Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5) and SQL Server 2022 RTM-CU25 (16.0.4255.1), Linux containers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights