SQL Server Alternatives to Cursor

Estimated study time: 9 minutes. Rewrite row-by-row logic as a single set-based statement.

Cursors process one row at a time, which rarely scales well. SQL Server's query engine is built around set-based operations, so most cursor logic can be rewritten to run faster - and with less code.

1. Plain Set-Based UPDATE/INSERT

A cursor that loops through rows to update each one individually can usually be replaced with a single UPDATE that applies to every matching row at once.

-- Instead of looping row by row to give a raise:
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'Sales';

2. Window Functions

Running totals, rankings, and row numbering - classic reasons to reach for a cursor - are built directly into SQL Server via window functions.

SELECT
    EmployeeId,
    Salary,
    SUM(Salary) OVER (ORDER BY EmployeeId) AS RunningTotal,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

3. Common Table Expressions (CTEs)

A recursive CTE can replace cursors used to walk hierarchical data, such as an organizational chart or a category tree.

WITH OrgChart AS (
    SELECT EmployeeId, ManagerId, Name, 1 AS Level
    FROM Employees WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.ManagerId, e.Name, o.Level + 1
    FROM Employees e
    JOIN OrgChart o ON e.ManagerId = o.EmployeeId
)
SELECT * FROM OrgChart ORDER BY Level;

4. CROSS APPLY / OUTER APPLY

When a cursor is used to run a per-row lookup (like fetching the latest order for each customer), CROSS APPLY does the same thing set-based.

SELECT c.CustomerId, o.OrderId, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 1 OrderId, OrderDate
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY o.OrderDate DESC
) o;

5. WHILE Loop (When You Truly Need Iteration)

If you genuinely need to iterate - say, over a small admin-only list - a WHILE loop on a temp table avoids cursor overhead entirely.

DECLARE @i INT = 1, @max INT;
SELECT @max = COUNT(*) FROM #TempIds;

WHILE @i <= @max
BEGIN
    -- process row @i
    SET @i += 1;
END;