Cursors in SQL Server: when they're fine, and how to replace them

A cursor lets T-SQL process a query’s result one row at a time: fetch a row, do something with it, fetch the next. It feels natural to anyone coming from C# or Java, which is why so many procedures contain one. It’s also usually the slowest way to do the job, because SQL Server is built to process sets of rows in one operation.

The examples run against the SpDevDemo database.

A cursor, written properly

The task: give every open order a 5% discount.

SET NOCOUNT ON;
DECLARE @started DATETIME2 = SYSDATETIME();
DECLARE @OrderId INT;

DECLARE open_orders CURSOR LOCAL FAST_FORWARD FOR
    SELECT OrderId FROM dbo.Orders WHERE Status = 'Open';

OPEN open_orders;
FETCH NEXT FROM open_orders INTO @OrderId;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Orders SET Total = Total * 0.95 WHERE OrderId = @OrderId;
    FETCH NEXT FROM open_orders INTO @OrderId;
END

CLOSE open_orders;
DEALLOCATE open_orders;

PRINT CONCAT('Cursor: ', DATEDIFF(MILLISECOND, @started, SYSDATETIME()), ' ms');

Cursor: 11319 ms

The options that matter:

• LOCAL: the cursor exists only in this batch or procedure. Without it, the default can be a global cursor, and a second call of the procedure in the same session fails with A cursor with the name … already exists.
• FAST_FORWARD: forward-only and read-only, the cheapest kind. Use it unless you need to move backwards or update through the cursor.
• CLOSE and DEALLOCATE, so it doesn’t hold resources until the session ends.
• @@FETCH_STATUS = 0 means a row was fetched. Test it after every FETCH.

The same job as one statement

SET NOCOUNT ON;
DECLARE @started DATETIME2 = SYSDATETIME();

UPDATE dbo.Orders SET Total = Total * 0.95 WHERE Status = 'Open';

PRINT CONCAT('Set-based: ', DATEDIFF(MILLISECOND, @started, SYSDATETIME()), ' ms');

Set-based: 131 ms

Same 6,000 rows, about 85 times faster. But it’s worth knowing why, because the reason isn’t what most people assume.

Outside an explicit transaction, each UPDATE in the loop is its own transaction, and every commit waits for its log record to be written to disk. Wrap the same cursor in BEGIN TRANSACTION ... COMMIT and measure again:

6,000 open orders, 5% discount	Time
Cursor, each UPDATE committed on its own	11,319 ms
Cursor, inside one transaction	155 ms
One UPDATE	131 ms

On this server, the 6,000 log flushes were almost the whole cost. With them gone, a cursor doing one trivial update per row is close to the set-based statement.

The gap comes back as soon as each row needs real work. Recomputing each order’s total from its lines, for the 18,000 open and shipped orders, with a cursor that sums the lines and updates the order for each row, inside one transaction, against the single UPDATE ... FROM shown further down:

18,000 orders, total recomputed from their lines	Time
Cursor, inside one transaction	566 ms
One UPDATE with a join	115 ms

The cursor runs 36,000 small statements, each seeking into an index. The set-based statement reads the order lines once, aggregates them with a hash, and joins the result to the orders in one pass. How big the difference is depends on the work per row (20% in the first test, 5 times in the second), but it rarely goes the cursor’s way, and on a server with slow log writes, a loop without a transaction is the worst of all.

WHILE loops are cursors too

Avoiding the CURSOR keyword doesn’t avoid the problem. This pattern is just as row-by-row:

DECLARE @id INT = 0;
WHILE 1 = 1
BEGIN
    SELECT TOP (1) @id = OrderId FROM dbo.Orders WHERE Status = 'Open' AND OrderId > @id ORDER BY OrderId;
    IF @@ROWCOUNT = 0 BREAK;
    UPDATE dbo.Orders SET Total = Total * 0.95 WHERE OrderId = @id;
END

It runs two statements per row instead of one, and without an index on (Status, OrderId) its SELECT TOP (1) can scan on every iteration. It’s no improvement on a FAST_FORWARD cursor.

Rewriting row-by-row logic as sets

Most cursors fall into a few patterns, each with a set-based equivalent.

Update each row from a calculation: a single UPDATE, with CASE for the branches the loop had as IFs.

UPDATE dbo.Orders
SET Total = Total * CASE WHEN Total > 2000 THEN 0.90 ELSE 0.95 END
WHERE Status = 'Open';

Update each row from related rows: UPDATE ... FROM with a join or an aggregate.

UPDATE o
SET Total = l.LineTotal
FROM dbo.Orders AS o
JOIN (SELECT OrderId, SUM(Quantity * UnitPrice) AS LineTotal
      FROM dbo.OrderLines GROUP BY OrderId) AS l
  ON l.OrderId = o.OrderId
WHERE o.Status = 'Open';

Running totals, previous row, numbering: window functions (SUM() OVER, LAG, ROW_NUMBER).

Insert into one table and capture the new IDs for another: INSERT ... OUTPUT inserted.Id INTO @ids (or MERGE ... OUTPUT when you need the source columns too).

Top N per group: ROW_NUMBER() or CROSS APPLY.

When a cursor is the right tool

Row-by-row isn’t always wrong. Use a loop when:

• Each row needs a procedure call that can’t take a set, such as sp_send_dbmail or a legacy procedure you can’t change. (If you can change it, give it a table-valued parameter.)
• Each row runs dynamic SQL, like maintenance across every database or every table.
• The work must be done in small batches, to keep transactions short and the log small. Here, loop over batches, not rows:

WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.AuditLog WHERE LoggedAt < '2024-01-01';
    IF @@ROWCOUNT = 0 BREAK;
END

• The logic depends on the previous row’s result in a way window functions can’t express. Even then, try a recursive CTE or a quirky-but-documented approach before a cursor on millions of rows.

Finding the loops that matter

A procedure can contain several loops, and not all of them are slow. A loop over 12 rows is fine; one over 50,000 rows that runs a lookup each time isn’t. You find out which by measuring each statement including how many times it ran. That’s exactly what SpDev Pro shows when it profiles a procedure: each line’s total cost and execution count (×6000), and a row-by-row finding when the statements inside a loop or cursor dominate the run.