SQL Server cursors: when to use them and how to replace them

A cursor lets SQL Server fetch rows one at a time. That is useful when an operation genuinely depends on processing each row separately. For ordinary data changes, though, a single statement usually expresses the task more clearly and gives the optimiser more room to choose an efficient plan.

This guide uses SQL Server T-SQL. The examples use temporary tables, so you can run them in a query window without changing permanent data. Measure your own workload before claiming a speed improvement: row count, indexes, triggers and concurrent work all matter.

First ask what the loop does

Cursor body	Try first	Important check
Copies selected rows	INSERT ... SELECT	Confirm the target's keys and defaults.
Updates rows from a lookup	UPDATE ... FROM	Ensure each target row matches at most one source row.
Calculates a running value	A window function	Define a deterministic row order.
Calls an external service or runs per-object maintenance	Keep a controlled loop	Record failures and make retries safe.

The decisive question is whether the next row needs the result of the previous row. Many loops merely repeat the same independent operation for every row.

Replace an update cursor with one statement

Suppose a staging table contains one approved status per customer. A cursor could fetch each row and issue an UPDATE. Here is a set-based version:

CREATE TABLE #Customer (
    CustomerId int PRIMARY KEY,
    Status varchar(20) NOT NULL
);
CREATE TABLE #StatusChange (
    CustomerId int PRIMARY KEY,
    NewStatus varchar(20) NOT NULL
);

INSERT INTO #Customer (CustomerId, Status)
VALUES (1, 'Pending'), (2, 'Pending'), (3, 'Active');
INSERT INTO #StatusChange (CustomerId, NewStatus)
VALUES (1, 'Active'), (2, 'Closed');

UPDATE c
SET c.Status = s.NewStatus
FROM #Customer AS c
JOIN #StatusChange AS s
  ON s.CustomerId = c.CustomerId
WHERE c.Status <> s.NewStatus;

SELECT CustomerId, Status FROM #Customer ORDER BY CustomerId;

The primary key on #StatusChange.CustomerId is deliberate. If two source rows match one target row, SQL Server does not promise which source value an UPDATE ... FROM will use. Deduplicate the source or define a key before updating. See Microsoft's UPDATE documentation and our worked update-join guide.

Replace a copy loop with INSERT SELECT

When every qualifying source row becomes one destination row, you do not need to fetch and insert each row separately:

CREATE TABLE #Incoming (
    SourceId int PRIMARY KEY,
    FullName varchar(100) NOT NULL,
    IsActive bit NOT NULL
);
CREATE TABLE #Directory (
    SourceId int PRIMARY KEY,
    FullName varchar(100) NOT NULL
);

INSERT INTO #Incoming (SourceId, FullName, IsActive)
VALUES (1, 'A Patel', 1), (2, 'B Jones', 0);

INSERT INTO #Directory (SourceId, FullName)
SELECT SourceId, FullName
FROM #Incoming
WHERE IsActive = 1;

SELECT SourceId, FullName FROM #Directory;

Keeping the source key in the destination makes later reconciliation simple. If you need generated destination IDs, SQL Server's OUTPUT INSERTED... can capture them. A plain INSERT ... SELECT cannot use the SELECT source alias inside OUTPUT to produce an old-to-new key mapping. Plan that mapping explicitly, for example with a stable source key in the destination. See the OUTPUT clause documentation and our audit-changes example.

Replace a running-total loop with a window function

If the cursor walks transactions in order to build a cumulative amount, SUM() OVER is often a better fit:

CREATE TABLE #Ledger (
    EntryId int PRIMARY KEY,
    AccountId int NOT NULL,
    EntryDate date NOT NULL,
    Amount decimal(12,2) NOT NULL
);

INSERT INTO #Ledger (EntryId, AccountId, EntryDate, Amount)
VALUES (1, 10, '2026-01-01', 100.00),
       (2, 10, '2026-01-02', -25.00),
       (3, 10, '2026-01-02', 10.00);

SELECT AccountId, EntryId, EntryDate, Amount,
       SUM(Amount) OVER (
           PARTITION BY AccountId
           ORDER BY EntryDate, EntryId
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningAmount
FROM #Ledger
ORDER BY AccountId, EntryDate, EntryId;

EntryId breaks ties between entries on the same date. Without an explicit tie-breaker, the intended order is ambiguous. For more window-function patterns, see LAG and LEAD for analysts.

When a cursor still makes sense

Some tasks are procedural: executing a maintenance command for each database, calling an external system one item at a time, or applying a rule whose next step truly depends on the previous result. A cursor can be reasonable then. Keep its input small, choose an explicit order when order matters, handle failures, and close and deallocate it. LOCAL FAST_FORWARD is a useful read-only, forward-only option, but it is not a universal performance setting. Microsoft's DECLARE CURSOR reference defines its behaviour.

Check a rewrite before shipping it

1. Run the original and replacement against the same representative data in a safe environment.
2. Compare which rows and values changed, including nulls and duplicate keys; row counts alone are insufficient.
3. Review execution plans and use SET STATISTICS IO, TIME ON for comparable runs.
4. Test with realistic indexes, triggers and concurrent activity.
5. Put multi-statement changes in a transaction where they must succeed or fail together.

A set-based rewrite is valuable when it preserves the business rule and behaves better on your workload. Start with the simplest correct statement, then measure.