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:
While loops
Cursor loops
Scalar UDFs called per row
Triggers doing row‑by‑row validations
Code that queries inside loops
The problem is simple: SQL Server is optimised for set‑based operations. RBAR neutralises 40 years of query engine optimisation.
Common symptoms include:
High CPU
Excessive reads
Table/row locks escalating
Long-running stored procedures
Deadlocks due to row-level operations extending transaction durations
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
Reduced from hundreds of row-level updates to one statement.
No looping, fewer locks, far fewer reads.
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
Supports bulk operations.
No per-row procedure calls.
Deterministic and set-based.
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
Fetch all parts for work order.
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
Prefer JOINS over nested selects.
Avoid scalar UDFs; use inline table-valued functions.
Avoid multi-statement TVFs.
Write triggers assuming multi-row inserts.
Use window functions instead of per-row queries.
Use MERGE cautiously and only when needed.
Store aggregated values only when necessary and with strict data governance.
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.