Introduction

Row‑By‑Agonizing‑Row (RBAR) is a term coined by SQL legend Jeff Moden. It refers to SQL code that processes data one row at a time rather than using SQL Server’s powerful set‑based engine. RBAR patterns appear harmless in small systems, but in enterprise transactional systems, they cripple throughput, introduce locking deadlocks, and degrade overall performance.

This article provides a practical, production‑ready guide to identifying RBAR patterns and rewriting them into scalable, high‑performance set‑based solutions. All explanations are in simple Indian English, with real‑world use cases and step‑by‑step rewrites.

Why RBAR Is Dangerous in Transactional Systems

RBAR usually shows up inside:

The problem is simple: SQL Server is optimised for set‑based operations. RBAR neutralises 40 years of query engine optimisation.

Common symptoms include:

Identifying RBAR Patterns

Below are the most common RBAR red flags.

1. Cursors

DECLARE cur CURSOR FOR
SELECT Id FROM Stockline WHERE Status = 'A';

OPEN cur;
FETCH NEXT FROM cur INTO @Id;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Stockline SET Quantity = Quantity - 1 WHERE Id = @Id;
    FETCH NEXT FROM cur INTO @Id;
END
CLOSE cur; DEALLOCATE cur;

2. While Loops

WHILE EXISTS(SELECT 1 FROM #Temp)
BEGIN
   SELECT TOP 1 @Id = Id FROM #Temp;
   EXEC ProcessOneItem @Id;
   DELETE FROM #Temp WHERE Id = @Id;
END

3. Scalar Functions in SELECT

SELECT Id, dbo.GetQuantity(Id) FROM Stockline;

Each row calls the function separately.

4. Triggers with Row‑Based Logic

IF EXISTS(SELECT * FROM inserted)
BEGIN
    SELECT @Id = Id FROM inserted; -- Wrong for multi-row inserts
    EXEC ValidateRow @Id;
END

5. Nested-for-each SQL from Application Code

foreach(var id in ids)
   Execute("UPDATE ... WHERE Id=" + id);

General Strategy to Remove RBAR

Refactoring RBAR is a systematic process:

Step 1: Identify the row-by-row logic

What is being done inside the loop? Update? Validation? Aggregation?

Step 2: Understand the intended business rule

Often, logic looks row‑based but is conceptually set‑based.

Step 3: Replace loops/cursors with set-based operations

Use

Step 4: Test output correctness first, then performance

Set-based rewrites must preserve business correctness.

Rewrite Examples (Practical)

This section shows real transactional patterns and their set-based rewrites.

Example 1: Cursor-Based Update Rewrite

RBAR Version

DECLARE cur CURSOR FOR
SELECT Id, QtyUsed FROM WorkOrderParts;

OPEN cur;
FETCH NEXT FROM cur INTO @Id, @QtyUsed;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Stockline
    SET QuantityAvailable = QuantityAvailable - @QtyUsed
    WHERE PartId = @Id;

    FETCH NEXT FROM cur INTO @Id, @QtyUsed;
END
CLOSE cur; DEALLOCATE cur;

Set-Based Rewrite

UPDATE s
SET s.QuantityAvailable = s.QuantityAvailable - w.QtyUsed
FROM Stockline s
INNER JOIN WorkOrderParts w ON s.PartId = w.Id;

Result

Example 2: Loop Processing of Temporary Table

RBAR Version

WHILE EXISTS(SELECT 1 FROM #ReserveParts)
BEGIN
    SELECT TOP 1 @Id = PartId, @Qty = Qty FROM #ReserveParts;
    UPDATE Stockline SET ReservedQty += @Qty WHERE PartId = @Id;
    DELETE FROM #ReserveParts WHERE PartId = @Id;
END

Set-Based Rewrite

UPDATE s
SET s.ReservedQty = s.ReservedQty + t.Qty
FROM Stockline s
JOIN #ReserveParts t ON s.PartId = t.PartId;

Example 3: Trigger RBAR Fix

RBAR Trigger

SELECT @Id = Id FROM inserted;
EXEC ValidateStock @Id;

Fails for multi-row inserts.

Set-Based Trigger Rewrite

INSERT INTO StockValidationLog (StockId, IsValid, CreatedOn)
SELECT i.Id,
       CASE WHEN s.Quantity >= 0 THEN 1 ELSE 0 END,
       GETDATE()
FROM inserted i
JOIN Stockline s ON s.Id = i.Id;

Result

Example 4: Scalar Function Rewrite Using APPLY

RBAR Version

SELECT Id, dbo.GetOpenQty(Id) AS OpenQty
FROM Stockline;

Optimised Version

Refactor the function logic as a joinable table expression.

SELECT s.Id, q.OpenQty
FROM Stockline s
CROSS APPLY (
    SELECT SUM(Quantity) AS OpenQty
    FROM Transactions t
    WHERE t.StockId = s.Id
) q;

Result

Example 5: Aggregation Loop Rewrite

RBAR Version

DECLARE @Total DECIMAL(18,2) = 0;
DECLARE @Amount DECIMAL(18,2);

DECLARE c CURSOR FOR SELECT Amount FROM Payments;
OPEN c;
FETCH NEXT FROM c INTO @Amount;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Total = @Total + @Amount;
    FETCH NEXT FROM c INTO @Amount;
END

SELECT @Total AS Total;

Set-Based Rewrite

SELECT SUM(Amount) AS Total FROM Payments;

Techniques for Removing RBAR

1. Using MERGE for Multi-Column Updates

MERGE allows batch operations on target tables.

MERGE Stockline AS t
USING UpdatedStock AS s ON t.Id = s.Id
WHEN MATCHED THEN
    UPDATE SET t.Quantity = s.Quantity;

2. Using Window Functions for Running Totals

SELECT Id,
       Amount,
       SUM(Amount) OVER (ORDER BY CreatedOn) AS RunningTotal
FROM Payments;

3. Using CROSS APPLY for Row-Specific Calculations

SELECT o.Id, ca.TotalQty
FROM Orders o
CROSS APPLY (
    SELECT SUM(Quantity)
    FROM OrderLines l
    WHERE l.OrderId = o.Id
) ca;

4. Using GROUP BY Instead of Manual Loops

SELECT PartId, SUM(QtyUsed) AS TotalUsed
FROM WorkOrderParts
GROUP BY PartId;

Real-World Case Study: Removing RBAR from Inventory Processing

A legacy inventory system suffered from deadlocks due to cursor-based quantity adjustments.

Original Process

  1. Fetch all parts for work order.

  2. For each part:

    • Reduce available qty

    • Increase reserved qty

    • Insert audit

RBAR Version

Contained two cursors and three nested procedure calls.

Final Set-Based Rewrite

;WITH Adjust AS (
    SELECT PartId,
           SUM(UsedQty) AS UsedQty,
           SUM(ReserveQty) AS ReserveQty
    FROM WorkOrderParts
    WHERE WorkOrderId = @WorkOrderId
    GROUP BY PartId
)

UPDATE s
SET    s.QuantityAvailable = s.QuantityAvailable - a.UsedQty,
       s.QuantityReserved = s.QuantityReserved + a.ReserveQty
FROM Stockline s
JOIN Adjust a ON a.PartId = s.Id;

INSERT INTO AuditTable (PartId, WorkOrderId, UsedQty, ReserveQty)
SELECT PartId, @WorkOrderId, UsedQty, ReserveQty
FROM Adjust;

Impact

Testing After Removing RBAR

What to validate:

Load Testing

Use:

Ensure high concurrency safety.

Best Practices for Writing Set-Based SQL

Summary

RBAR is one of the biggest performance killers in transactional SQL systems. Removing it requires careful analysis but usually leads to dramatic improvements in speed, scalability, and stability. By rewriting loops, cursors, and row-based logic to set-based queries, systems become more predictable, more efficient, and easier to maintain.