DATE_CORRELATION_OPTIMIZATION: Helping Joins on Related Dates

DATE_CORRELATION_OPTIMIZATION teaches SQL Server that two date columns move together, like an order date and a ship date. When you filter on one, it can add a matching range on the other. That can save a lot of reads, but it is not free.

The problem: you filter one date, the join reads the other table

Picture the Monday report: all orders from last week with their shipments. You filter Orders by OrderDate. The Shipments table is clustered on ShipDate, but nothing in your query says where those dates are. SQL Server cannot guess that shipments follow orders by a few days. So it reads far more of Shipments than it needs.

The option fixes that by keeping a small hidden record of how the two dates relate. The demo creates the SqlAuthorityDemo database and drops it at the end.

Build two tables that qualify

The setup is picky. Both columns are datetime and lead the clustered index of their table. A foreign key joins the tables. Dates that merely look related are not enough.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.Orders
(OrderId int NOT NULL PRIMARY KEY NONCLUSTERED, OrderDate datetime NOT NULL,
 INDEX CX_Orders CLUSTERED (OrderDate));

CREATE TABLE dbo.Shipments
(ShipmentId int NOT NULL PRIMARY KEY NONCLUSTERED, OrderId int NOT NULL, ShipDate datetime NOT NULL,
 INDEX CX_Shipments CLUSTERED (ShipDate),
 CONSTRAINT FK_Shipments_Orders FOREIGN KEY (OrderId) REFERENCES dbo.Orders (OrderId));

INSERT dbo.Orders VALUES (1, '20250110'), (2, '20250210');
INSERT dbo.Shipments VALUES (1, 1, '20250112'), (2, 2, '20250213');

Turn the option on and find the hidden view

It is a database setting. The first query shows it is on. The second finds the helper SQL Server created, a view whose name starts with _MPStats_Sys_ and ends with the foreign key name. The screenshot shows both flags as 1. I left out the long generated view name.

ALTER DATABASE SqlAuthorityDemo SET DATE_CORRELATION_OPTIMIZATION ON;

SELECT is_date_correlation_on FROM sys.databases WHERE name = N'SqlAuthorityDemo';
SELECT name, is_date_correlation_view FROM sys.views WHERE is_date_correlation_view = 1 ORDER BY name;

Check that the answer does not change

An optimization must never change results. This query asks for January orders and returns the single January order with its shipment.

SELECT o.OrderId, s.ShipmentId, s.ShipDate
FROM dbo.Orders AS o JOIN dbo.Shipments AS s ON s.OrderId = o.OrderId
WHERE o.OrderDate >= '20250101' AND o.OrderDate < '20250201'
ORDER BY o.OrderId, s.ShipmentId;

Measure the difference with real volume

Two rows prove nothing about speed. So I add 100,000 more orders from March 2025 on, each shipped one to three days later. The January query above is not affected.

WITH n AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 100 AS k
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
INSERT dbo.Orders (OrderId, OrderDate)
SELECT k, DATEADD(MINUTE, k * 5, '20250301') FROM n;

INSERT dbo.Shipments (ShipmentId, OrderId, ShipDate)
SELECT OrderId + 100, OrderId, DATEADD(DAY, 1 + OrderId % 3, OrderDate)
FROM dbo.Orders
WHERE OrderId > 100;

Now ask for two days of orders with the option still on. Open the Messages tab in SSMS and look at the Shipments line. It reads 57 pages and finds 576 shipments.

SET STATISTICS IO ON;

SELECT COUNT(*) AS shipments_found
FROM dbo.Orders AS o JOIN dbo.Shipments AS s ON s.OrderId = o.OrderId
WHERE o.OrderDate >= '20250601' AND o.OrderDate < '20250603';

SET STATISTICS IO OFF;

Now switch the option off and run the same query. The count is still 576, but Shipments needs 312 page reads. That is more than five times the work for the same answer. Your ratio will differ, because it depends on how tightly your dates are linked.

ALTER DATABASE SqlAuthorityDemo SET DATE_CORRELATION_OPTIMIZATION OFF;
GO
SET STATISTICS IO ON;

SELECT COUNT(*) AS shipments_found
FROM dbo.Orders AS o JOIN dbo.Shipments AS s ON s.OrderId = o.OrderId
WHERE o.OrderDate >= '20250601' AND o.OrderDate < '20250603';

SET STATISTICS IO OFF;

Remember the cost for writers

The hidden view has to stay current, so inserts and date changes on the related tables do extra work. I did not time that here, because the cost depends on your workload. Test your inserts and updates, not only your reads.

One more warning. Do not copy the idea by hand and add your own ship date filter. A late shipment would fall outside your guess, and the report would quietly lose rows. Let SQL Server derive the range.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Keep the option only if your own reads gain more than your writes pay.

Date correlation is not free speed, it is a trade you measure.

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