Rewriting Correlated Subqueries as Window Functions

Counting the same related rows repeatedly in a SELECT list deserves a closer look. Rewriting correlated subqueries with window functions can express those calculations over a shared ordered input. Preserve the original result meaning first, then compare the plans and IO that SQL Server actually produces.

Identify Correlated Subqueries That Ask About One Group

A correlated subquery references a value from its outer row. In a SELECT list, several subqueries can each ask about the same customer's orders. One counts the group, another finds the latest date, and another calculates a cumulative amount. That structure expresses the rule clearly but can lead to repeated access.

I check the actual plan before claiming repeated scans. The optimizer can transform or combine work, so three subqueries in the text do not guarantee three full scans per output row. The rewrite is a candidate to test, not an observed performance result.

Window functions retain detail rows while adding calculations over their partitions. They fit these group-level and ordered calculations well. They do not automatically replace every correlated operation. A subquery that decides whether a row belongs in the result answers a different question from one that adds a value to a retained row.

Replace correlated subqueries only after matching their filter scope and treatment of duplicates to the proposed window expression.

Capture the Original Result With Stable Ordering

The temporary table contains synthetic orders, including two on the same date. OrderID supplies a deterministic tie breaker for cumulative progress. The correlated running-total condition includes earlier dates and earlier or equal identifiers on the same date.

Run all examples in the same SSMS connection. Enable actual plans and retain STATISTICS IO output. The original statement is the reference result for the rewrite. Compare values by OrderID rather than assuming identical grid presentation proves equality.

I include ties deliberately because they expose a common rewrite mistake. Ordering the window only by OrderDate would leave row-by-row progress ambiguous, or use peer-aware behavior under a default frame. The original condition defines exactly which prior orders count. The rewritten window must express that same definition, not a simpler-looking approximation. The extra query has earned its place only if it answers the same question.

CREATE TABLE #WindowOrders(OrderID int PRIMARY KEY,CustomerID int,OrderDate date,Amount decimal(12,2));
INSERT #WindowOrders VALUES(1,10,'20260920',10),(2,10,'20260920',20),(3,10,'20260921',30),(4,20,'20260921',15);
SET STATISTICS IO ON;
SELECT o.OrderID,o.CustomerID,o.OrderDate,o.Amount,
 (SELECT COUNT_BIG(*) FROM #WindowOrders AS x WHERE x.CustomerID=o.CustomerID) AS OrderCount,
 (SELECT MAX(x.OrderDate) FROM #WindowOrders AS x WHERE x.CustomerID=o.CustomerID) AS LatestOrderDate,
 (SELECT SUM(x.Amount) FROM #WindowOrders AS x WHERE x.CustomerID=o.CustomerID
 AND(x.OrderDate<o.OrderDate OR(x.OrderDate=o.OrderDate AND x.OrderID<=o.OrderID))) AS RunningAmount
FROM #WindowOrders AS o ORDER BY o.CustomerID,o.OrderDate,o.OrderID;
SET STATISTICS IO OFF;

Express the Three Calculations as Windows

COUNT_BIG OVER PARTITION BY CustomerID adds the customer's total count to every detail row. MAX OVER the same partition adds the latest order date. SUM with the partition, ordering, and explicit ROWS frame adds the cumulative amount through the current order.

The next query preserves the original columns and the tie-breaking sequence. The final ORDER BY controls presentation separately from the window definition. Without it, SQL Server does not promise to return rows in the window's order.

Compare the actual plans with identical input. Window processing can introduce sorts, spools, or memory requirements. A suitable index can provide useful order, but the chosen access path still needs inspection. The correct comparison includes both saved table reads and any new supporting work. In my run, the original reported more scans of the temporary table, while the window version added worktable reads. A rewrite does not receive a performance exemption merely because the SQL text became easier to explain.

SET STATISTICS IO ON;
SELECT OrderID,CustomerID,OrderDate,Amount,
 COUNT_BIG(*) OVER(PARTITION BY CustomerID) AS OrderCount,
 MAX(OrderDate) OVER(PARTITION BY CustomerID) AS LatestOrderDate,
 SUM(Amount) OVER(PARTITION BY CustomerID ORDER BY OrderDate,OrderID
 ROWS UNBOUNDED PRECEDING) AS RunningAmount
FROM #WindowOrders
ORDER BY CustomerID,OrderDate,OrderID;
SET STATISTICS IO OFF;

Keep Filtering Correlated Subqueries When They Fit

EXISTS commonly expresses whether a related row qualifies the outer row. A window count does not remove rows by itself, and forcing a join plus window into that role can duplicate the outer data. Keep a well-designed EXISTS filter when it expresses the requirement cleanly.

The next query finds customer identifiers having an order above a deliberate test threshold. Its correlated EXISTS is useful and readable. A supporting index and the actual plan determine its cost. The presence of correlation alone does not make it a target for removal.

What must the query return: detail rows, one row per customer, or only qualifying customers? Answer that before selecting a rewrite. Window functions, grouped aggregates, and existence checks have different row-shape behavior. A tuning change that accidentally changes that shape can appear faster because it quietly did less of the required work. Keep cardinality and duplicate behavior in the correctness test.

SELECT c.CustomerID
FROM(SELECT DISTINCT CustomerID FROM #WindowOrders) AS c
WHERE EXISTS
(SELECT 1 FROM #WindowOrders AS o WHERE o.CustomerID=c.CustomerID AND o.Amount>20);

Support the Partition and Sequence Intentionally

For a recurring customer-order calculation, an index beginning with CustomerID, OrderDate, and OrderID can provide the partition and sequence needed by the windows. Include Amount when coverage is justified by the wider workload. That index has write and storage costs, so do not create it solely from this small sample.

Filtering before the window changes the population being counted and summed. If the report needs lifetime totals beside a filtered display, calculate the lifetime windows before applying the display filter. If it needs only period totals, filter the input first. Those two placements express different business meanings.

Check NULL amounts and dates as well. SUM ignores NULL amounts, while ordering NULL dates follows SQL Server's ordering semantics. Decide whether such rows are valid and how they belong in the sequence. The window expression cannot repair an undefined date rule by itself. Preserve the raw rows for exceptions rather than hiding them in a broad filter.

Validate the Rewrite Under Representative Data

Test one customer, several customers, tied dates, no qualifying rows, and NULL inputs allowed by the real schema. Compare the original and rewritten outputs in both directions, including identifiers and every calculated value. A matching grand total does not prove every running total matches.

Then compare actual IO, memory grants, spills, and execution behavior on representative volume. SQL Server 2012 and later support the window features used here. Keep the tested database level and relevant indexes with the evidence, because they influence the chosen plans.

Rewriting correlated subqueries is useful when it expresses shared calculations clearly and reduces repeated work. Preserve detail-row semantics, use an explicit cumulative frame, and leave filtering operations in the form that best expresses their rule. Keep the version that your correctness checks and actual workload evidence support.

Related reading on this blog: T-SQL Window Function Framing and Performance: Notes from the Field #103 and What’s the Difference between ROW_NUMBER, RANK, and DENSE_RANK? Notes from the Field #096.

A shorter SELECT list is not a tuning result, it is a rewrite that must preserve meaning and reduce useful work.

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