Why You Should Avoid SQL Cursors and What to Use Instead

Introduction

SQL Server (and most relational databases) are built around the concept of set‑based processing – treat a whole result set as a single unit rather than looping row‑by‑row. Yet many developers still reach for cursors, especially when they come from procedural languages such as Python or C#.

While cursors have legitimate uses, they are often the hidden cause of sluggish stored procedures, long‑running batch jobs and unnecessary resource consumption. This article explains why you should avoid SQL cursors, quantifies the performance impact, and provides a toolbox of set‑based alternatives that data analysts and developers at DataAnalyst.co.uk can adopt today.

---

1. The Hidden Cost of Cursors

Issue	Typical Impact	Real‑world Example
Row‑by‑row execution	Each fetch incurs a round‑trip to the engine, turning a single logical operation into thousands of physical calls.	A cursor updating 200 million rows took 30 hours; the same logic rewritten with a set‑based join completed in 17 hours (plus indexing time) – a ≈80 % reduction in runtime.
Lock escalation & blocking	Cursors often hold locks for the whole fetch cycle, increasing dead‑lock risk.	In a 204 k‑row outer cursor, only 0.5 % of loops had completed after 30 hours, keeping locks on large tables for days.
Memory pressure	Fast‑forward or static cursors materialise the full result set in tempdb, consuming I/O and space.	A static cursor on a 10 million‑row table filled tempdb, causing paging and slowing unrelated workloads.
Parallelism loss	Cursors force a serial execution plan, preventing SQL Server from using multiple cores.	Converting a cursor to a set‑based INSERT … SELECT allowed the query to run with parallelism degree = 4, dramatically speeding up CPU utilisation.
Maintainability	Procedural loops are harder to read, test and optimise than declarative set statements.	A 150‑line cursor routine required three debugging sessions; the set‑based rewrite fit on a single page and was covered by existing unit tests.

Bottom line: In most OLTP and analytical workloads, cursors are 10‑100× slower than an equivalent set‑based approach.

---

2. When (If Ever) a Cursor Is Appropriate

Even seasoned DBAs agree that cursors are not always forbidden. Acceptable scenarios include:

1. Row‑by‑row business logic that cannot be expressed in set terms – e.g. complex hierarchical traversals where each step depends on the previous row’s outcome.
2. Maintenance tasks on system catalogs – such as rebuilding indexes one at a time while monitoring system load.
3. Small‑scale data‑migration scripts where the volume is tiny (< 1 000 rows) and readability outweighs performance concerns.

If you find yourself in one of these cases, consider:

• Using a FAST_FORWARD cursor (read‑only, forward‑only) to minimise overhead.
• Limiting the result set with precise WHERE clauses.
• Explicitly setting READ_ONLY and SCROLL_LOCKS to avoid unnecessary locking.

---

3. Set‑Based Alternatives: The Toolbox

Below is a concise guide to the most common set‑based techniques that replace cursor logic.

3.1 Simple INSERT … SELECT / UPDATE … FROM

For bulk inserts or updates where values come from another table:

INSERT INTO dbo.Destination (FullName, CreatedOn)
SELECT FullName, GETDATE()
FROM dbo.Source
WHERE IsActive = 1;

UPDATE d
SET    d.Status = s.NewStatus
FROM   dbo.Destination d
JOIN   dbo.Source s ON d.SourceId = s.SourceId;

3.2 The MERGE Statement

MERGE combines INSERT, UPDATE and DELETE in a single set‑based operation and pairs nicely with the OUTPUT clause to capture identity values.

MERGE INTO dbo.Target t
USING dbo.Staging s
ON (t.Key = s.Key)
WHEN MATCHED THEN
    UPDATE SET t.Value = s.Value
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Key, Value) VALUES (s.Key, s.Value)
OUTPUT inserted.TargetId, s.SourceId INTO dbo.Mapping;

Tip: If you only need INSERT/UPDATE, the MERGE syntax can be simplified to avoid the controversial “WHEN NOT MATCHED BY SOURCE” delete clause, which some experts (e.g., Aaron Bertrand) advise against.

3.3 OUTPUT Clause on INSERT/UPDATE/DELETE

Capture the rows affected without a temp table or cursor.

DECLARE @InsertedRows TABLE (DestId INT, SrcId INT);

INSERT INTO dbo.Destination (FullName)
OUTPUT INSERTED.DestinationId, src.SourceId INTO @InsertedRows
SELECT FullName
FROM   dbo.Source src;

Now @InsertedRows holds the mapping of source rows to newly generated identity values – exactly what a cursor often tries to achieve.

3.4 Common Table Expressions (CTEs)

CTEs let you build intermediate result sets that can be referenced multiple times, avoiding repeated scans.

WITH Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Category ORDER BY CreatedOn DESC) AS rn
    FROM   dbo.Transactions
)
UPDATE t
SET    t.IsLatest = CASE WHEN r.rn = 1 THEN 1 ELSE 0 END
FROM   dbo.Transactions t
JOIN   Ranked r ON t.TransactionId = r.TransactionId;

3.5 Table‑Valued Parameters (TVPs)

When you need to pass a collection of rows from an application layer, TVPs replace a cursor‑style loop entirely.

CREATE TYPE dbo.CustomerIds AS TABLE (CustomerId INT PRIMARY KEY);
GO

CREATE PROCEDURE dbo.UpdateCustomerStatus
    @Ids dbo.CustomerIds READONLY
AS
BEGIN
    UPDATE c
    SET    Status = 'Active'
    FROM   dbo.Customers c
    JOIN   @Ids i ON c.CustomerId = i.CustomerId;
END;

The client builds the TVP in memory and sends it in a single round‑trip.

3.6 APPLY Operators (CROSS APPLY / OUTER APPLY)

When you need to invoke a table‑valued function per row, APPLY does it set‑wise and often outperforms a cursor loop.

SELECT o.OrderId, v.ProductList
FROM   dbo.Orders o
CROSS APPLY dbo.GetOrderProducts(o.OrderId) v;

---

4. Practical Conversion: From Cursor to Set‑Based

4.1 The Problem Cursor

DECLARE @SourceId INT, @DestId INT, @Name VARCHAR(50);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT SourceId, Name FROM dbo.Source;

OPEN cur;
FETCH NEXT FROM cur INTO @SourceId, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.Destination (Name) VALUES (@Name);
    SET @DestId = SCOPE_IDENTITY();

    UPDATE dbo.Source
    SET DestinationId = @DestId
    WHERE SourceId = @SourceId;

    FETCH NEXT FROM cur INTO @SourceId, @Name;
END;

CLOSE cur;
DEALLOCATE cur;

4.2 Set‑Based Rewrite Using OUTPUT

DECLARE @Mapping TABLE (SourceId INT, DestId INT);

INSERT INTO dbo.Destination (Name)
OUTPUT inserted.DestinationId, src.SourceId INTO @Mapping (DestId, SourceId)
SELECT Name
FROM   dbo.Source src;

UPDATE s
SET    s.DestinationId = m.DestId
FROM   dbo.Source s
JOIN   @Mapping m ON s.SourceId = m.SourceId;

Result: The operation now runs in seconds on a 100 k‑row table, compared with minutes for the cursor. It also removes the need for a procedural loop, making the code easier to read and test.

4.3 When You Must Keep Row‑by‑Row Logic

If the business rule truly depends on the previous row’s outcome (e.g., cumulative balance with custom rounding), consider:

• Using a recursive CTE – still set‑based but can express row‑wise calculations.
• Performing the calculation in the client application where a loop is natural, then bulk‑loading the results.

---

5. Tips for Refactoring Existing Cursor Code

1. Profile first – Use sys.dm_exec_query_stats and SET STATISTICS IO, TIME ON to capture the current cost.
2. Identify the data flow – What columns are read, what rows are written? Map this to a SELECT … INTO or MERGE.
3. Leverage temporary tables – SELECT … INTO #Tmp can replace a cursor that builds a mapping table.
4. Add appropriate indexes – Set‑based joins benefit from covering indexes; test the plan with SET SHOWPLAN_TEXT ON.
5. Validate with row counts – Ensure the set‑based version produces the same row count and checksum as the cursor version.
6. Monitor parallelism – MAXDOP may need tuning after conversion; set‑based queries often gain from parallel execution.
7. Document the why – Future developers should understand the performance motivation behind the change.

---

Conclusion

SQL cursors are a relic of a time when relational engines lacked the sophisticated optimisation engines they possess today. In modern SQL Server (2017 and later) and other RDBMS, set‑based operations are the default path to speed, scalability, and maintainability.

By recognising the hidden costs—excessive I/O, lock contention, loss of parallelism—and replacing cursors with joins, MERGE, OUTPUT, CTEs, TVPs, or APPLY, you can:

• Reduce execution time by up to 90 % for large batches.
• Free up resources for concurrent workloads.
• Simplify code for easier debugging and future enhancements.

For data analysts and developers at DataAnalyst.co.uk, the takeaway is simple: reach for set‑based constructs first, keep cursors as a last resort, and always benchmark the before‑and‑after. Your queries will run faster, your servers will stay healthier, and your stakeholders will appreciate the quicker insights.

---