A narrow date filter gains little when the same date range spans nearly every compressed rowgroup. An ordered clustered columnstore can build tighter SaleDate segments and improve rowgroup elimination. Compare the stored ranges and actual scan evidence before assuming the ORDER definition solved the query.
Connect Date Ranges With Segment Elimination
Each compressed columnstore segment stores metadata used to decide whether it can contain qualifying values. If every rowgroup covers almost the entire date domain, a narrow date query still has to inspect many segments. Better ordering can reduce that overlap and let the scan skip irrelevant groups.
I inspect segment ranges when a selective date filter performs broad columnstore work. The predicate can be correct and eligible while the stored layout leaves little to eliminate. Query text and physical distribution need separate review.
SQL Server 2022 introduces ordered clustered columnstore indexes. The ORDER clause identifies columns used during the build's ordering process. It does not replace ORDER BY for a query's returned rows. Keep the physical-layout purpose distinct from presentation order. The index can organize today's build carefully without promising that every future insert will politely stand in line behind it.
Prepare Identical Tables With Mixed Dates
The setup creates synthetic sales data with repeating dates distributed through the load. It then copies the same columns into a second table. Use a disposable database and enough resources for the chosen test population, which is deliberately large enough to investigate several rowgroups.
The generated population is an input setting, not a claimed observed row count. If your test environment needs a different size, retain that choice with the results and verify how many compressed groups were actually created. A single-rowgroup test cannot demonstrate much elimination between groups.
I keep both tables' values identical before building the indexes. That makes their aggregates comparable under the same filter. Do not load one table with a different period or distribution and attribute the resulting IO difference solely to ordering. Physical build settings, memory, and concurrency also belong in the experiment record so another person can understand how the layouts were produced.
CREATE TABLE dbo.UnorderedSalesDemo(ID bigint,SaleDate date,Amount decimal(12,2));
WITH Numbers AS
(
SELECT TOP(3000000) ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.UnorderedSalesDemo
SELECT n,DATEADD(day,CONVERT(int,n%1000),'20200101'),CONVERT(decimal(12,2),n%100) FROM Numbers;
SELECT ID,SaleDate,Amount INTO dbo.OrderedSalesDemo FROM dbo.UnorderedSalesDemo;
Build the Ordered Clustered Columnstore With a Controlled Degree
The next block builds one ordinary clustered columnstore and one ordered by SaleDate. MAXDOP one keeps the demonstration's build degree controlled and can produce tighter ordered ranges than a build divided among several sorting streams. That choice can make the build slower, so it is a tradeoff to measure.
Available memory and sorting behavior also affect build quality. Do not describe MAXDOP one as a universal production requirement or a guarantee of perfect segment boundaries. Compare the resulting metadata and the workload's scan behavior.
The ORDER column list matters. A leading column unrelated to the frequent predicate can change how useful later columns' ranges become. Choose ordering keys from the actual analytical access patterns rather than copying SaleDate into every design. A table queried primarily by another domain needs its own review. The index definition should express the layout opportunity that the workload can actually use.
CREATE CLUSTERED COLUMNSTORE INDEX CCI_UnorderedSalesDemo
ON dbo.UnorderedSalesDemo WITH(MAXDOP=1);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderedSalesDemo
ON dbo.OrderedSalesDemo ORDER(SaleDate) WITH(MAXDOP=1);Compare Encoded Segment Ranges Carefully
sys.column_store_segments exposes min_data_id and max_data_id for each segment. Join through sys.partitions and sys.columns to identify the table and SaleDate column. Keep segment identifiers, row counts, and range values together when comparing layouts.
These min and max fields are encoded metadata, not automatically display-ready dates. Do not cast them directly into invented human-readable dates. Compare their range and overlap patterns with the supported encoding context, then confirm elimination through actual execution evidence.
What changed in the ordered table's ranges? Look for narrower, less overlapping groups for the selected column. The exact boundaries and number of groups come from your build. A metadata pattern is useful evidence, but the query still needs to show that its predicate takes advantage of it. Keep both tables' results in the same capture so a later rebuild does not silently replace one side of the comparison.
SELECT OBJECT_NAME(p.object_id) AS TableName,p.partition_number,s.segment_id,
s.row_count,s.min_data_id,s.max_data_id,s.on_disk_size
FROM sys.column_store_segments AS s
JOIN sys.partitions AS p ON p.partition_id=s.partition_id
JOIN sys.columns AS c ON c.object_id=p.object_id AND c.column_id=s.column_id
WHERE p.object_id IN(OBJECT_ID(N'dbo.UnorderedSalesDemo'),OBJECT_ID(N'dbo.OrderedSalesDemo'))
AND c.name=N'SaleDate'
ORDER BY TableName,p.partition_number,s.min_data_id,s.segment_id;Read Segments Skipped in the Actual Query
Enable actual plans and STATISTICS IO, then run the same date-range aggregate against each table. Inspect columnstore segment reads and segments skipped in the messages and the scan's actual properties. The half-open date range avoids accidentally including the next period's boundary.
The next queries use a date window inside the synthetic data's domain. Confirm equal aggregate results before comparing the physical work. An empty range outside the loaded domain is a different test and can make elimination look impressive without representing the real request.
Compare representative narrow and broad periods. Ordering helps most when the predicate can exclude ranges, while a query needing the whole table has a different opportunity. Keep your actual IO and timing evidence instead of promising a fixed improvement. The useful conclusion connects the tighter stored ranges with reduced work for the workload that motivated the build.
SET STATISTICS IO ON;
SELECT SUM(Amount) AS SalesAmount FROM dbo.UnorderedSalesDemo
WHERE SaleDate>='20200201' AND SaleDate<'20200301';
SELECT SUM(Amount) AS SalesAmount FROM dbo.OrderedSalesDemo
WHERE SaleDate>='20200201' AND SaleDate<'20200301';
SET STATISTICS IO OFF;Revisit an Ordered Clustered Columnstore After Later Loads
Later inserts do not maintain one global sorted sequence across existing compressed groups. New loads can create overlapping ranges, and modifications can change the layout's usefulness. The original build's good elimination is therefore a baseline to monitor, not a permanent property to assume.
A reviewed rebuild can restore useful ordering under the index definition, with its CPU, memory, storage, and log costs planned. The example below retains a controlled build degree for that maintenance comparison. Recheck segment ranges and the captured query afterward.
An ordered clustered columnstore is useful when its physical ranges serve recurring selective analytics. Choose the key, control the build experiment, and measure segments skipped. Keep later load patterns and maintenance costs in the decision. The last check is the same as the first: does the current layout reduce the work of the real date query?
ALTER INDEX CCI_OrderedSalesDemo ON dbo.OrderedSalesDemo REBUILD WITH(MAXDOP=1);Related reading on this blog: Columnstore Segment Elimination: Loading Data So Scans Skip Work and Columnstore Rowgroup Health: Finding Small and Open Rowgroups.
Columnstore ordering is not a permanent insertion guarantee, it is a physical layout whose usefulness needs follow-up.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
