Using cursors to update rows one-by-one

Use set-based UPDATE with WHERE clause

Cursors fetch and process rows one-by-one, causing massive overhead. Each row requires: FETCH (network round trip), process (CPU), UPDATE (disk I/O). For 1M rows, that is 1M round trips. Set-based operations process all rows in single statement with optimized execution plan.

Example - Update order totals:

-- WRONG: Cursor approach (10 minutes for 1M rows)

DECLARE @OrderID INT, @Total DECIMAL(10,2);

DECLARE cursor_orders CURSOR FOR

SELECT order_id FROM orders WHERE status = 'pending';

OPEN cursor_orders;

FETCH NEXT FROM cursor_orders INTO @OrderID;

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Total = SUM(quantity * unit_price)

FROM order_items WHERE order_id = @OrderID;

UPDATE orders SET total_amount = @Total WHERE order_id = @OrderID;

FETCH NEXT FROM cursor_orders INTO @OrderID;

END;

CLOSE cursor_orders;

DEALLOCATE cursor_orders;

-- CORRECT: Set-based approach (5 seconds for 1M rows)

UPDATE o

SET total_amount = item_totals.total

FROM orders o

JOIN (

SELECT order_id, SUM(quantity * unit_price) AS total

FROM order_items

GROUP BY order_id

) item_totals ON o.order_id = item_totals.order_id

WHERE o.status = 'pending';

Cursors are acceptable only for: 1) Administrative tasks (backup, maintenance), 2) Complex business logic that cannot be expressed in set-based SQL, 3) Processing external API calls per row.

Always try to solve problems with set-based SQL (UPDATE, INSERT, DELETE with WHERE) before considering cursors. If you must use cursors, use FAST_FORWARD option for better performance.

Critical

Cursor processes 1M rows in 10 minutes, set-based UPDATE processes same in 5 seconds

Not using TRY/CATCH in transactions, letting errors leave transactions open

Wrap all transactions in TRY/CATCH with ROLLBACK in CATCH block

When an error occurs in a transaction without TRY/CATCH, the transaction remains open. This holds locks on affected rows/tables, blocking other queries. In high-traffic systems, this causes cascading failures.

Example - Transfer money between accounts:

-- WRONG: No error handling (transaction left open on error)

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 100;