A repeatable query-tuning workflow

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Hold result meaning fixed while testing a predicate rewrite.

Run the example

SELECT COUNT(*) AS OrdersInJanuary
FROM dbo.Orders
WHERE OrderDate >= CONVERT(date, '2023-01-01')
  AND OrderDate < CONVERT(date, '2023-02-01');

Read it step by step

1. Write down the expected result and dataset version before tuning.
2. The half-open range expresses January dates without wrapping OrderDate in YEAR or MONTH.
3. Compare actual plans, logical reads, and elapsed times under repeatable conditions; this result table alone cannot establish a speedup.

Check the result

The query counts calendar dates in January 2023; its date boundaries are explicit.

Verify equal result sets

WITH RangeIDs AS (
    SELECT OrderID FROM dbo.Orders
    WHERE OrderDate >= CONVERT(date, '2023-01-01')
      AND OrderDate < CONVERT(date, '2023-02-01')
), FunctionIDs AS (
    SELECT OrderID FROM dbo.Orders
    WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 1
)
SELECT
    (SELECT COUNT(*) FROM (SELECT OrderID FROM RangeIDs EXCEPT SELECT OrderID FROM FunctionIDs) AS x) AS OnlyRange,
    (SELECT COUNT(*) FROM (SELECT OrderID FROM FunctionIDs EXCEPT SELECT OrderID FROM RangeIDs) AS x) AS OnlyFunction;

Both differences are zero on the unmodified v1.0.0 dataset. This compares matching OrderID sets, not just their counts.

Explore it further

For the performance comparison, run the January query once with the half-open range and once with YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 1 on the same disposable dataset. Enable Actual Execution Plan and SET STATISTICS IO ON; compare operators and logical reads over repeated runs. Turn STATISTICS IO OFF afterward. The range allows an index search condition, but a faster plan is not guaranteed for this dataset.

Practice

Count February 2023 orders with a half-open date range.

SELECT COUNT(*) AS OrdersInFebruary
FROM dbo.Orders
WHERE OrderDate >= CONVERT(date, '2023-02-01')
  AND OrderDate < CONVERT(date, '2023-03-01');