Row-by-Row No More: Rewriting Cursor-Based MS-SQL Procedures into Set-Based Queries

Why Cursors Hurt Performance

Cursors are tempting because they look procedural and familiar:

• You fetch one row.
• You run some logic.
• You move to the next row.

The problem is that SQL Server is built to work with sets of rows, not one row at a time. Cursors:

• Execute the same operations thousands or millions of times.
• Prevent the optimiser from doing global, set-level optimisations.
• Often hold locks longer than necessary.
• Are harder to reason about and to parallelise.

Set-based queries, on the other hand:

• Express what you want, not how to loop.
• Let SQL Server choose efficient join, sort, and aggregation strategies.
• Are usually much shorter and easier to maintain.

You won’t always get a 10x improvement, but when you remove a cursor from a hot path (nightly jobs, ETL, financial calculations), the difference is often very noticeable.

A Realistic Cursor Example

Imagine a simple sales schema:

CREATE TABLE dbo.SalesOrder
(
    SalesOrderID   INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID     INT NOT NULL,
    OrderDate      DATE NOT NULL,
    Amount         DECIMAL(18,2) NOT NULL,
    Status         VARCHAR(20) NOT NULL
);

CREATE TABLE dbo.CustomerBalance
(
    CustomerID     INT PRIMARY KEY,
    Balance        DECIMAL(18,2) NOT NULL,
    LastUpdated    DATETIME2(0) NOT NULL
);

A legacy stored procedure updates customer balances for all orders in a date range, row by row:

CREATE OR ALTER PROCEDURE dbo.UpdateCustomerBalances_Cursor
    @FromDate DATE,
    @ToDate   DATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @SalesOrderID INT,
        @CustomerID   INT,
        @Amount       DECIMAL(18,2);

    DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT SalesOrderID, CustomerID, Amount
        FROM dbo.SalesOrder
        WHERE OrderDate >= @FromDate
          AND OrderDate <  DATEADD(DAY, 1, @ToDate)
          AND Status = 'Closed';

    OPEN order_cursor;

    FETCH NEXT FROM order_cursor 
        INTO @SalesOrderID, @CustomerID, @Amount;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Upsert-like logic per row
        IF EXISTS (SELECT 1 FROM dbo.CustomerBalance WHERE CustomerID = @CustomerID)
        BEGIN
            UPDATE dbo.CustomerBalance
            SET Balance     = Balance + @Amount,
                LastUpdated = SYSDATETIME()
            WHERE CustomerID = @CustomerID;
        END
        ELSE
        BEGIN
            INSERT INTO dbo.CustomerBalance (CustomerID, Balance, LastUpdated)
            VALUES (@CustomerID, @Amount, SYSDATETIME());
        END

        FETCH NEXT FROM order_cursor 
            INTO @SalesOrderID, @CustomerID, @Amount;
    END

    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END;

This is very common:

• Upsert per row.
• One SELECT, one UPDATE or INSERT per order.
• Lots of round-trips inside the engine.

Let’s rewrite it.

Step 1: Replace Row Logic with Aggregations

Look at what the cursor actually does:

• For each order in the date range:
  • Add Amount to that customer’s balance.

We don’t care about individual orders in the balance table, only the sum per customer.

So we can pre-aggregate:

;WITH DailyCustomerTotals AS
(
    SELECT
        so.CustomerID,
        SUM(so.Amount) AS TotalAmount
    FROM dbo.SalesOrder AS so
    WHERE so.OrderDate >= @FromDate
      AND so.OrderDate <  DATEADD(DAY, 1, @ToDate)
      AND so.Status = 'Closed'
    GROUP BY so.CustomerID
)

This CTE now represents the final change per customer. No loops needed.

Step 2: Use a Single MERGE or Split INSERT/UPDATE

We now want to:

• Update existing customers in CustomerBalance.
• Insert new customers that don’t exist yet.

Option A: Use MERGE

MERGE expresses this in one statement:

CREATE OR ALTER PROCEDURE dbo.UpdateCustomerBalances_SetBased
    @FromDate DATE,
    @ToDate   DATE
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH DailyCustomerTotals AS
    (
        SELECT
            so.CustomerID,
            SUM(so.Amount) AS TotalAmount
        FROM dbo.SalesOrder AS so
        WHERE so.OrderDate >= @FromDate
          AND so.OrderDate <  DATEADD(DAY, 1, @ToDate)
          AND so.Status = 'Closed'
        GROUP BY so.CustomerID
    )
    MERGE dbo.CustomerBalance AS tgt
    USING DailyCustomerTotals AS src
        ON tgt.CustomerID = src.CustomerID
    WHEN MATCHED THEN
        UPDATE SET
            tgt.Balance     = tgt.Balance + src.TotalAmount,
            tgt.LastUpdated = SYSDATETIME()
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerID, Balance, LastUpdated)
        VALUES (src.CustomerID, src.TotalAmount, SYSDATETIME());
END;

This removes the cursor entirely and lets SQL Server handle:

• Join between CustomerBalance and the aggregated totals.
• Bulk update and insert.

Option B: Separate UPDATE and INSERT

If you prefer to avoid MERGE (for readability or to sidestep edge cases), you can split it:

CREATE OR ALTER PROCEDURE dbo.UpdateCustomerBalances_SetBased
    @FromDate DATE,
    @ToDate   DATE
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH DailyCustomerTotals AS
    (
        SELECT
            so.CustomerID,
            SUM(so.Amount) AS TotalAmount
        FROM dbo.SalesOrder AS so
        WHERE so.OrderDate >= @FromDate
          AND so.OrderDate <  DATEADD(DAY, 1, @ToDate)
          AND so.Status = 'Closed'
        GROUP BY so.CustomerID
    )
    -- Update existing customers
    UPDATE cb
    SET cb.Balance     = cb.Balance + dct.TotalAmount,
        cb.LastUpdated = SYSDATETIME()
    FROM dbo.CustomerBalance AS cb
    JOIN DailyCustomerTotals AS dct
        ON cb.CustomerID = dct.CustomerID;

    -- Insert new customers
    INSERT INTO dbo.CustomerBalance (CustomerID, Balance, LastUpdated)
    SELECT dct.CustomerID, dct.TotalAmount, SYSDATETIME()
    FROM DailyCustomerTotals AS dct
    LEFT JOIN dbo.CustomerBalance AS cb
        ON cb.CustomerID = dct.CustomerID
    WHERE cb.CustomerID IS NULL;
END;

Both approaches are set-based and usually far quicker than the cursor.

Before/After Benchmarking: How to Measure It Properly

You don’t need fancy tools to see the difference. Use what SQL Server already gives you.

1. Enable Basic Timing

In SSMS:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

EXEC dbo.UpdateCustomerBalances_Cursor 
    @FromDate = '2024-01-01', 
    @ToDate   = '2024-01-31';

EXEC dbo.UpdateCustomerBalances_SetBased 
    @FromDate = '2024-01-01', 
    @ToDate   = '2024-01-31';

Compare:

• CPU time and elapsed time.
• Logical reads.

The set-based version should:

• Run in a fraction of the time for larger datasets.
• Use fewer logical reads because it touches data in a more predictable way.

2. Use a Repeatable Test Dataset

For meaningful comparisons:

1. Create a test database.
2. Populate SalesOrder with a realistic volume, e.g.:
   • Tens or hundreds of thousands of rows.
   • Multiple orders per customer.
3. Run both procedures multiple times to warm up the cache.
4. Capture average timings over several runs.

You’ll typically see the cursor version grow nearly linearly with row count, while the set-based version scales more smoothly.

3. Check Execution Plans

Look at the actual execution plans for both procedures:

• The cursor plan will show repeated operations (lookups, updates) inside a loop.
• The set-based plan will show joins and a single update/insert operation.

You want to see:

• Index seeks instead of scans where possible.
• Reasonable join strategies (hash/merge, not nested loops on large sets).

Common Patterns for Cursor-to-Set Refactoring

Most cursor-based procedures fall into a few patterns. Here’s how to think about each.

1. Row-by-Row Aggregation

Pattern:

• Cursor loops through rows.
• Maintains running totals or counters.

Set-based replacement:

• Use GROUP BY with SUM, COUNT, MIN, MAX, etc.
• Use window functions (SUM() OVER (PARTITION BY ...)) when you need per-row context.

Example window function:

SELECT
    CustomerID,
    OrderDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningBalance
FROM dbo.SalesOrder;

2. Conditional Row Updates

Pattern:

• Cursor checks conditions and updates different columns depending on logic.

Set-based replacement:

• Use CASE expressions inside a single UPDATE.

UPDATE so
SET Status = CASE 
                 WHEN PaymentReceivedDate IS NOT NULL THEN 'Paid'
                 WHEN DueDate < CAST(GETDATE() AS DATE) THEN 'Overdue'
                 ELSE Status
             END
FROM dbo.SalesOrder AS so
WHERE Status IN ('Open', 'Pending');

3. Dependent Calculations (Order Matters)

Pattern:

• Cursor processes rows in a specific order (e.g., by date).
• Each row depends on the result of the previous row.

Set-based replacement:

• Use window functions and/or recursive CTEs.

Running stock levels example:

;WITH OrderedMovements AS
(
    SELECT
        ProductID,
        MovementDate,
        QuantityChange,
        ROW_NUMBER() OVER (
            PARTITION BY ProductID 
            ORDER BY MovementDate, MovementID
        ) AS rn
    FROM dbo.StockMovement
),
RecursiveStock AS
(
    SELECT
        ProductID,
        MovementDate,
        QuantityChange,
        rn,
        CAST(QuantityChange AS INT) AS RunningStock
    FROM OrderedMovements
    WHERE rn = 1

    UNION ALL

    SELECT
        om.ProductID,
        om.MovementDate,
        om.QuantityChange,
        om.rn,
        rs.RunningStock + om.QuantityChange
    FROM OrderedMovements AS om
    JOIN RecursiveStock AS rs
        ON om.ProductID = rs.ProductID
       AND om.rn = rs.rn + 1
)
SELECT *
FROM RecursiveStock
OPTION (MAXRECURSION 0);

This still isn’t as fast as pure aggregation, but it’s usually better than a cursor and keeps the logic declarative.

When You Might Still Use a Cursor

Cursors aren’t forbidden; they’re just expensive. They can still be appropriate when:

• You truly need to call external code per row (e.g., complex stored procedures, external APIs via CLR, etc.).
• The row count is small and will remain small (dozens, not thousands).
• The logic cannot be expressed in SQL’s set-based model without becoming unreadable or error-prone.

Even then:

• Prefer FAST_FORWARD or READ_ONLY cursors when possible.
• Keep the cursor scope as narrow as you can.
• Document why a cursor is used and the expected row counts.

Practical Takeaway: Start with Your Hottest Cursor

Don’t aim to remove every cursor tomorrow. Instead:

1. Find the top resource-consuming stored procedures (Query Store, DMVs, or your monitoring tool).
2. Identify which of those use cursors.
3. Rewrite one high-impact cursor using the patterns above:
   • Replace row-by-row logic with GROUP BY and window functions.
   • Use MERGE or split UPDATE/INSERT instead of per-row upserts.
4. Benchmark before and after with SET STATISTICS IO/TIME ON and keep the numbers.

Once you’ve seen the improvement on a real procedure, it becomes much easier to justify and prioritise similar refactors across your codebase.