Replacing a Cursor With a Set-Based Query

The cursor works, but every row takes another trip through the same logic. Replacing a cursor with a set-based query can make both the rule and its cost easier to see.

Write Down What Each Iteration Does Before Replacing a Cursor

A cursor is sometimes the right tool, but many loops repeat one relational operation. Start by describing the loop’s input, lookup, calculation, and output. If each order row receives a rate from a customer table, the work is a join and an update. SQL Server already knows how to perform that operation over a set.

I keep the cursor version long enough to compare results. The goal is not to prove that loops are embarrassing. The goal of replacing a cursor is to prove that the new query preserves the business rule. List conditions that skip rows, handle NULL values, and choose a lookup match before rewriting anything.

Ask whether one row’s result depends on the previous row’s update. If it does, a single UPDATE needs a different model. If rows are independent under a stable source snapshot, a set operation is the natural starting point. That distinction prevents a fast rewrite that quietly changes meaning.

Use a Small Worked Example

Suppose OrderCharge has OrderId, CustomerId, Amount, and Fee. CustomerRate has one active rate per customer. The cursor fetches each unpaid order, looks up its rate, and writes Amount multiplied by Rate. A missing rate leaves the order for review. The set-based version can express the same eligibility in one joined UPDATE.

A unique key on CustomerRate for the active record matters. If several rates match one order, the result of an UPDATE join can be ambiguous. Make the source relation one-to-one first. Do not rely on a cursor’s accidental fetch order to choose a rate.

I test orders with a rate, without a rate, with a zero amount, and with an existing fee. The sample should include the edges, not just the happy row. The code below updates only rows with a matching rate and a NULL fee.

UPDATE oc
SET Fee = ROUND(oc.Amount * cr.Rate, 2)
FROM dbo.OrderCharge AS oc
JOIN dbo.CustomerRate AS cr
  ON cr.CustomerId = oc.CustomerId
 AND cr.IsActive = 1
WHERE oc.Fee IS NULL;

Check the Rows the Join Would Touch

Before the UPDATE, run the join as a SELECT. Inspect keys, old values, proposed values, and duplicate matches. A preview query is especially useful when the source lookup can change. It shows whether the set has the grain you think it has.

I also count unmatched rows. A cursor can skip them quietly, while a joined UPDATE also leaves them untouched. The new procedure should log that count and decide whether it is acceptable. A missing rate is a data quality issue, not a reason to manufacture zero.

Keep the preview and update predicates aligned. If the SELECT filters one date range and the UPDATE filters another, the preview gives false confidence. Use shared parameters or a staged candidate table when the rule is complex.

SELECT oc.OrderId, oc.Amount, oc.Fee AS OldFee,
       cr.Rate, ROUND(oc.Amount * cr.Rate, 2) AS ProposedFee
FROM dbo.OrderCharge AS oc
JOIN dbo.CustomerRate AS cr
  ON cr.CustomerId = oc.CustomerId
 AND cr.IsActive = 1
WHERE oc.Fee IS NULL;

Protect the Update With Constraints

A set-based statement can touch many rows quickly. That is useful only when its eligibility is correct. Check the expected target keys first, run inside a transaction during rehearsal, and confirm the changed row count. The permanent procedure should have a clear error path and a tested rollback plan.

If the rate table permits duplicate active records, fix that rule with a unique filtered index or a staging validation before the update. The optimizer cannot infer your intended winner from a business sentence. A deterministic source makes the SQL easier to reason about.

I avoid adding TOP to make a bad join feel safer. TOP can split work into batches, but it does not solve ambiguous matching. First make one row mean one result. Then batch for log pressure or lock duration when the workload justifies it.

Compare Correctness Before Speed When Replacing a Cursor

Run the cursor and the set-based statement against equivalent copies or in separate repeatable test runs. Compare target keys, fee values, NULL counts, and totals. EXCEPT in both directions can find differing rows. A matching row count alone cannot catch two wrong values that cancel each other.

I save the result of the comparison query, not a remembered impression. If rounding or type conversion differs, inspect those rows and settle the rule. A rewrite that runs faster but bills a different fee is a failed rewrite. The result contract comes first.

Use a transaction or disposable test database to restore the same starting state for each method. Cache warmth and prior updates can change timing. The test needs identical data and parameters to say anything useful about the code.

SELECT OrderId, Fee
FROM dbo.OrderCharge_CursorResult
EXCEPT
SELECT OrderId, Fee
FROM dbo.OrderCharge_SetResult;

SELECT OrderId, Fee
FROM dbo.OrderCharge_SetResult
EXCEPT
SELECT OrderId, Fee
FROM dbo.OrderCharge_CursorResult;

Measure the Whole Operation Fairly When Replacing a Cursor

Use SET STATISTICS IO and SET STATISTICS TIME during a controlled test, or capture representative Query Store data. Include cursor setup, fetches, updates, and cleanup in the cursor measurement. Include transaction and logging cost for both methods. Look at reads, CPU, duration, locks, and log growth.

I do not put a made-up speedup in a post. Your table size, indexes, and row width decide the result. A set-based update can still scan too much or block readers. Inspect the actual plan and waits before declaring victory.

Repeat the test enough to separate one cold run from a stable pattern. Keep other workload in view. A shorter statement that causes a longer blocking queue is not an operational improvement.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) AS remaining
FROM dbo.OrderCharge
WHERE Fee IS NULL;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Know When a Cursor Still Fits

Some work must call an external operation for each item, or each step depends on the result of the previous step. A cursor or controlled loop can be appropriate there. Keep its transaction scope and retry behavior explicit. Do not hide a network call inside a large SQL transaction.

I ask whether the loop is processing data or coordinating actions. SQL Server is strong at set processing. Coordination across independent external systems is a different job. A loop can be readable when it owns that sequence and logs each step.

The useful habit is to challenge row-by-row code with a set-shaped question. Describe the input relation, the desired output relation, and the matching rule. Then test both correctness and workload cost on the server that will run it.

What question should you ask before removing the loop? Check whether order is part of the requirement. A set-based UPDATE has no guaranteed row processing order, and a calculation that depends on the preceding row needs an explicit window definition or another design. I compare the old and new outputs on duplicates, NULL keys, and rows with no match before changing production code.

Related reading on this blog: Convert Cursor to Set Based Insert and Replacing a Cursor with a Common Table Expression.

Replacing a cursor is not a contest against loops, it is a clearer expression of independent row work.

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