Set-based SQL processes an entire set of rows in one statement and lets the optimizer choose an efficient plan; a cursor (or WHILE loop) processes one row at a time — row-by-row, sometimes called RBAR ("Row-By-Agonizing-Row").
Set-based operations are usually dramatically faster because the optimizer can use indexes and parallelism and avoids the per-row overhead, locking, and round-trips that cursors incur.
Replacing a cursor with a set-based statement is one of the largest single performance wins available in T-SQL — differences of hundreds or thousands of times are common. This guide explains why, and the narrow cases where a cursor is still the right call.
The core difference
SQL Server is a relational engine built to operate on sets. A set-based statement (a single UPDATE, INSERT…SELECT, or MERGE) describes what you want and lets the optimizer decide how. A cursor instead dictates a procedural how — fetch a row, process it, repeat — which fights the engine's strengths.
Why cursors are slow
- Per-row overhead. Row-by-row processing requires repeated round-trips and bookkeeping for every row, which adds up fast over large sets.
- State and memory. Cursors maintain state for each row being processed, consuming more server resources than a single set operation.
- Locking and blocking. Cursors often hold locks for extended periods, increasing contention and the risk of deadlocks.
- The optimizer can't help. SQL Server's optimizer is built to optimize set-based operations and is far less effective at row-by-row iteration.
The magnitude is not subtle
Published comparisons are stark: a cursor that took 48 seconds where the equivalent set-based query took 87 milliseconds, and a nested-cursor job that had completed only about 0.5% of its work after 30 hours — replaced by a set-based approach that finished in roughly a day. Exact ratios vary, but set-based wins are routinely orders of magnitude.
Converting RBAR to set-based
The shift is mental: stop thinking "for each row" and start thinking "for the whole set." A loop that updates one row at a time becomes a single statement that updates them all:
Row-by-row → set-based-- RBAR: a cursor/loop updating one row at a time
-- (fetch row, UPDATE ... WHERE Id = @Id, repeat)
-- Set-based: one statement, one optimized plan
UPDATE o
SET o.Status = 'Shipped'
FROM dbo.Orders AS o
JOIN dbo.Shipments AS s ON s.OrderId = o.OrderId
WHERE s.ShippedDate IS NOT NULL;
The set-based version lets SQL Server use indexes and, where worthwhile, parallelism — and it measures out as a sharp drop in logical reads and duration.
When a cursor is actually fine
Cursors are not forbidden — they are just the wrong default. They are reasonable for genuinely sequential or administrative work: iterating over databases or objects to run maintenance, calling a stored procedure once per row, or one-off scripts where each step depends on the previous one. For set-oriented data manipulation in production code, prefer set-based every time.
As with any rewrite, a cursor-to-set-based conversion must return identical results — verify equivalence before trusting it. That discipline is the core of the optimization loop.
How automated analysis catches RBAR
Row-by-row patterns are a recognizable code smell, and converting them to set-based logic is one of the higher-impact rewrites an AI analysis can propose. SprocOptimizer can flag row-by-row processing during analysis and suggest a set-based rewrite — which, like every change, is verified for logical equivalence and validated against your real parameters before it can be promoted.
Frequently asked questions
Set-based processing handles an entire set of rows in a single statement and lets the query optimizer build one efficient plan. Cursor (or WHILE-loop) processing handles one row at a time — row-by-row, sometimes called RBAR. Set-based is usually far faster because the optimizer can use indexes and parallelism and avoids per-row overhead.
Cursors process rows one at a time, which requires repeated round-trips and maintains state for each row, consuming more memory and CPU. They often hold locks for longer, increasing blocking, and SQL Server's optimizer — designed for set-based work — cannot optimize row-by-row iteration effectively. The result is far more overhead than the equivalent set-based statement.
The difference can be enormous. Published examples show a cursor taking 48 seconds where the equivalent set-based query took 87 milliseconds, and a nested-cursor job that had completed only about 0.5% of its work after 30 hours being replaced by a set-based approach that finished in roughly a day. The exact ratio depends on the workload, but set-based wins are routinely orders of magnitude.
Cursors are reasonable for genuinely sequential or administrative tasks — iterating over databases or objects to run maintenance, calling a stored procedure once per row, or one-off scripts where each step depends on the last. For set-oriented data manipulation in production code, a set-based statement is almost always the better choice.
Primary sources & further reading
- Microsoft Community Hub — Increase SQL Server performance by replacing cursors with set operations.
- Paul S. Randal, SQLskills — Reconciling set-based operations with row-by-row iterative processing.
- SQLShack — SQL Server cursor performance problems.
Turn agonizing loops into single statements
SprocOptimizer flags row-by-row patterns and proposes set-based rewrites, validated for identical results — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide