Clustered Key Choice for Date Range Queries: Date or Identity

Clustered key choice decides how your table is stored on disk, so it decides how cheap a month of orders is to read. An identity key keeps new rows in order of arrival. A date key keeps rows in order of date. Let me measure both on the same rows.

The monthly report question

Say finance asks for April’s orders every month, and the report feels slow. A developer says, “Cluster the table on OrderDate.” That sounds right. But the clustered key is not just a search helper. Every nonclustered index stores a copy of it, and every insert has to find its place in it.

So compare designs, not opinions. The demo builds 20,000 orders in an identity-clustered table. The dates are spread over two years and are not in the same order as the ids, like a table fed by imports and late corrections. Padding stands in for the other columns a real order table has.

DROP TABLE IF EXISTS #IdentityOrders;

CREATE TABLE #IdentityOrders
(
    OrderId   int IDENTITY PRIMARY KEY,
    OrderDate date NOT NULL,
    Amount    decimal(12, 2) NOT NULL,
    Padding   char(100) NOT NULL DEFAULT ''
);

INSERT #IdentityOrders (OrderDate, Amount)
SELECT TOP (20000)
       DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 37) % 730, '20250101'),
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 100 + 1
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

Build the date-clustered twin

The second table holds the same rows, but its clustered key is OrderDate plus OrderId. I add OrderId so the key is unique. Otherwise SQL Server adds a hidden uniquifier to duplicate dates, and that costs space.

DROP TABLE IF EXISTS #DateOrders;

CREATE TABLE #DateOrders
(
    OrderId   int NOT NULL,
    OrderDate date NOT NULL,
    Amount    decimal(12, 2) NOT NULL,
    Padding   char(100) NOT NULL,
    PRIMARY KEY CLUSTERED (OrderDate, OrderId)
);

SET IDENTITY_INSERT #IdentityOrders ON;
INSERT #DateOrders (OrderId, OrderDate, Amount, Padding)
SELECT OrderId, OrderDate, Amount, Padding FROM #IdentityOrders;
SET IDENTITY_INSERT #IdentityOrders OFF;

Read one month from each table

Now ask for April 2026 in both tables. I use a half-open range, from April 1 up to but not including May 1. That form works for date columns and for any time part. I return a count and a total, so the grid stays small and the reads show up in the messages.

SET STATISTICS IO ON;

SELECT COUNT(*) AS Orders, SUM(Amount) AS Total
FROM #IdentityOrders
WHERE OrderDate >= '20260401' AND OrderDate < '20260501';

SELECT COUNT(*) AS Orders, SUM(Amount) AS Total
FROM #DateOrders
WHERE OrderDate >= '20260401' AND OrderDate < '20260501';

SET STATISTICS IO OFF;

Both queries return the same answer: 822 orders and a total of 41357.00. The cost differs. In my run, the identity table needed 314 logical reads, because with no useful index it had to scan the whole table. The date-clustered table needed 16, because its rows for April sit side by side. Your counts may differ a little, but the gap should look similar.

Give the identity table a date index

You do not have to re-cluster to fix the report. A nonclustered index on OrderDate that includes Amount can answer the same question. It costs extra work on every insert, since each new order now updates two structures. Add it, and run the identity query again.

CREATE INDEX IX_IdentityOrders_Date ON #IdentityOrders (OrderDate) INCLUDE (Amount);

SET STATISTICS IO ON;

SELECT COUNT(*) AS Orders, SUM(Amount) AS Total
FROM #IdentityOrders
WHERE OrderDate >= '20260401' AND OrderDate < '20260501';

SET STATISTICS IO OFF;

Same answer again, 822 orders. In my run, the reads dropped from 314 to 6. The small index holds just the date and the amount, so SQL Server reads a thin slice of it. The date key won against the bare identity table, but the identity table with a date index did better still on this report.

Count the cost before you change the key

Changing the clustered key on a big table is a project. It rebuilds the table and every other index. It needs free space, log space, and a plan for primary keys and foreign keys. Test the whole change on a copy.

Weigh the whole workload. If most queries are month ranges, a date key may win. If most queries look up one order by id, or new rows arrive in date order anyway, identity plus a small date index is often the calmer choice. Either way, measure with your data. This demo only uses temp tables, and the last block drops them.

DROP TABLE IF EXISTS #DateOrders;
DROP TABLE IF EXISTS #IdentityOrders;

Pick the key with your whole workload in view, not just this week’s report.

A clustered key is not just a search preference, it is a table-wide storage decision.

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