Ordered Columnstore Indexes in SQL Server 2022

A date filter should not read every segment in a reporting table. An ordered columnstore index in SQL Server 2022 helps keep related values close together. That gives segment elimination a better chance to avoid irrelevant data.

Separate Segment Order From Row Order

Columnstore stores each column in compressed segments within rowgroups. A range predicate can skip a segment whose bounds exclude the requested values. When dates are scattered across rowgroups, those bounds overlap broadly.

Using ordered columnstore narrows that overlap. The engine can then reject more irrelevant segments before reading their contents.

I start by asking which columns appear in selective reporting filters. A date column is useful when requests focus on particular periods. Ordering by a value nobody filters on wastes the opportunity.

The order should reflect actual access patterns. An attractive distribution on paper doesn't make an expensive build worthwhile by itself.

SQL Server 2022 introduced ordered clustered columnstore indexes. The syntax uses ORDER after the columnstore declaration. It isn't WITH ORDER.

Also, this storage order doesn't promise sorted query results. A SELECT still needs ORDER BY when presentation order matters. Physical organization and the logical output contract are different responsibilities.

Create the Baseline Before the Ordered Columnstore

Run this example in a disposable database. The generated sales are synthetic input for practicing the commands. They aren't a measured workload.

Three million rows give the index three rowgroups, which is enough to see segments skipped. A tiny table fits in one rowgroup and shows nothing. For a real decision, use a representative reporting table with the same date distribution as production.

Create the conventional columnstore first and preserve its metadata output. Run a selective range query with STATISTICS IO enabled. Keep the actual plan too.

The baseline tells you whether existing segment bounds are already useful. There is little value in paying for a sort when the original load arrives well organized.

Don't clear shared caches to make a dramatic comparison. Repeat both versions under controlled conditions on a test server. Record CPU as well as reads.

Segment skipping describes one part of the work. Concurrent ingestion, storage behavior, and other operators still influence the total request. Those belong in the decision too.

CREATE TABLE dbo.OrderedSalesDemo
(
    SaleId int NOT NULL,
    SaleDate date NOT NULL,
    Amount decimal(19,4) NOT NULL
);
INSERT dbo.OrderedSalesDemo(SaleId, SaleDate, Amount)
SELECT n,
       DATEADD(day, n % 366, CONVERT(date,'20240101')),
       CONVERT(decimal(19,4), 25 + n % 50)
FROM (SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a
      CROSS JOIN sys.all_objects AS b) AS numbers;
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderedSalesDemo
ON dbo.OrderedSalesDemo;

Inspect the Bounds Without Decoding Internals

Use sys.column_store_segments to inspect the segment metadata. Join partitions to identify the object and its rowgroups. Include column_id so you know which column each segment belongs to.

Save the output before and after the ordered build. The segment count and overlapping bounds provide context for the read comparison.

The min_data_id and max_data_id columns are internal values. Don't convert them into dates and call that a supported decoding method. SQL Server 2022 also exposes min_deep_data and max_deep_data for expanded segment elimination.

Those values are internal too. They are useful metadata to retain, not an application interface for reconstructed dates.

I compare those bounds with the execution evidence instead of promising a hand-decoded calendar. Metadata alone doesn't prove the optimizer avoided segments. STATISTICS IO reports segment reads and skips for columnstore scans.

The plan shows the predicate and scan context. Together they explain whether the physical organization served the requested date range.

SELECT s.column_id, s.segment_id, s.row_count,
       s.min_data_id, s.max_data_id,
       s.min_deep_data, s.max_deep_data
FROM sys.column_store_segments AS s
JOIN sys.partitions AS p ON p.partition_id = s.partition_id
WHERE p.object_id = OBJECT_ID(N'dbo.OrderedSalesDemo')
ORDER BY s.column_id, s.segment_id;

Rebuild as an Ordered Columnstore by Date

Replace the existing index using DROP_EXISTING. ORDER names the column that guides the build. MAXDOP 1 is a deliberate test choice for stronger ordering in this SQL Server 2022 example.

Sorting takes resources and time. Don't assume the option is free because the final data is compressed.

Parallel builds distribute sorting work and can leave more overlap between segments. Newer releases add further build options, so match guidance to your version. For this test, keep the comparison simple and reproducible.

Record the build settings beside the result. Otherwise, two ordered builds can appear comparable while doing different physical work.

The operation needs a maintenance plan, including working space and log capacity. Confirm the supported edition and build behavior before scheduling it. An index that helps short reports still needs to fit the load window. This is the part where a clever read optimization becomes an operational responsibility for the DBA.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderedSalesDemo
ON dbo.OrderedSalesDemo
ORDER (SaleDate)
WITH (DROP_EXISTING = ON, MAXDOP = 1);

Keep the Range Predicate Searchable

Use a direct comparison against SaleDate. Wrapping that column in a formatting function obscures the simple range the engine needs. A half-open interval makes the endpoint clear.

It also adapts cleanly when the source becomes datetime2. Return the same aggregate in both tests so the requested work stays consistent.

Run this query once against the baseline and again after the rebuild. Read the Messages tab for segment reads, segments skipped and logical reads. On the synthetic data, every baseline rowgroup covers the whole year, so the March query reads every segment. After the ordered build, each segment covers its own slice of the year and the same query skips most of them.

Use your own results for the conclusion on real tables. The example doesn't promise a specific reduction.

If the range covers most of the table, skipping little data is the expected outcome. Ordering shines when the query can reject a substantial part of the stored domain.

Try more than one range. Include a recent period, an older period, and the broad report people run at month-end. A single favorable date window hides the rest of the workload.

Read the plan for each request. The final decision should reflect the reports you actually need, including the inconvenient ones.

SET STATISTICS IO ON;
SELECT SUM(Amount) AS TotalAmount
FROM dbo.OrderedSalesDemo
WHERE SaleDate >= '20240301' AND SaleDate < '20240401';
SET STATISTICS IO OFF;

Account for Loads and Changing Distribution

The ORDER declaration doesn't make every later insert arrive globally sorted forever. New compressed rowgroups reflect how data is loaded and maintained. Out-of-order arrivals and small batches affect segment overlap.

Check the metadata again after representative ingestion. A newly built index and a busy index are different stages of the same design.

Partitioning can help isolate periods, but it doesn't replace useful segment bounds inside each partition. Keep those choices separate when testing. Partition elimination rejects partitions.

Segment elimination rejects columnstore segments. Both reduce work at different levels. A clear explanation names which level improved and which predicate enabled that improvement.

Would your reports benefit more from this order than from faster loading? That is the trade to discuss with the team. Measure ingestion and reporting together.

Don't rebuild repeatedly without identifying a drift pattern. A maintenance schedule needs a reason beyond the fact that the option exists and the syntax is short.

Keep the Ordered Columnstore Only With Evidence

Retain the original index definition, baseline reads, and representative plans. Repeat the segment query after building the ordered version. Keep the new reads beside the same date ranges.

This lets another DBA review the choice without reconstructing your test. A result with context remains useful after the next load finishes.

If the bounds already suit the reports, keep the simpler design. If the ordered build rejects more irrelevant segments, weigh that benefit against build cost. Neither conclusion needs invented timings.

The server supplies the evidence. Your job is to ask a fair question and preserve the conditions under which it answered.

Recheck the choice when reporting filters or ingestion patterns change. The best ordering column for ordered columnstore follows the workload, not a permanent preference for dates. A columnstore index stores the data efficiently.

A useful physical order helps the engine avoid reading it. That second benefit is the reason to pay for the sort.

Related reading on this blog: Columnstore Segment Elimination: Loading Data So Scans Skip Work and Columnstore Rowgroup Health: Finding Small and Open Rowgroups.

Ordered storage is not a sorted result promise, it is a chance to skip irrelevant segments.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.