SQL Server Set-Based Thinking: Replacing Cursors with APPLY and CTEs (2026)

1. Set-Based vs Procedural Thinking

SQL Server was designed around the relational model, which treats data as mathematical sets. The query optimizer can only reason about, parallelise, and index-seek across entire sets of rows. When you write a cursor or WHILE loop, you bypass the optimizer entirely and hand-implement iteration that SQL Server was never designed to perform efficiently.

Procedural thinking says: "For each order, calculate the discount and update the row." Set-based thinking says: "Update the entire Orders set in one statement, computing the discount expression per row using a JOIN." The first approach generates one lock acquire/release cycle, one index lookup, and one log write per row. The second generates one operation for the entire affected set.

Why SQL Server is Optimised for Sets

• Cost-based optimizer — the query optimizer evaluates statistics and considers hash joins, merge joins, parallel scans, and indexed seeks across the full set. It cannot apply these strategies to a cursor loop.
• Parallelism — set-based operations can fan out across multiple CPU cores. A cursor's FETCH NEXT loop is inherently serial.
• Minimal logging — bulk set operations qualify for minimal logging in simple/bulk-logged recovery, slashing transaction log writes. Row-by-row operations are fully logged every time.
• Read-ahead I/O — when SQL Server knows it needs an entire table or index range, the storage engine performs read-ahead. Cursor fetches are single-row random reads.
• Lock escalation — a set-based UPDATE takes one or a few page/extent locks and may escalate to a table lock. A cursor holds and releases thousands of row-level locks sequentially, creating contention hot-spots.

2. Why Cursors Are Slow: Row-by-Row Overhead

A cursor is essentially a pointer that moves through a result set one row at a time. Each FETCH NEXT call involves a context switch, a separate logical I/O, a lock acquisition, and a round-trip through the execution engine. For a table with 100,000 rows, that is 100,000 individual operations.

The Anatomy of Cursor Overhead

-- Typical slow cursor pattern
DECLARE @OrderID   INT;
DECLARE @Discount  DECIMAL(5,2);

DECLARE order_cursor CURSOR FAST_FORWARD FOR
    SELECT OrderID FROM Sales.Orders WHERE Status = 'Pending';

OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderID;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Expensive per-row logic
    SET @Discount = dbo.CalculateDiscount(@OrderID);

    UPDATE Sales.Orders
    SET    Discount = @Discount
    WHERE  OrderID  = @OrderID;

    FETCH NEXT FROM order_cursor INTO @OrderID;
END;

CLOSE order_cursor;
DEALLOCATE order_cursor;

On a table of 500,000 pending orders, this pattern performs 500,000 individual UPDATE statements. Each one compiles (or plan-cache-hits), acquires a row lock, writes to the log, and releases the lock. The cumulative overhead is enormous.

Measuring the Cost

SET STATISTICS TIME, IO ON;

-- Run your cursor block here, then compare with the set-based version.
-- Look for:
--   "Scan count"     → high = lots of random seeks (bad)
--   "Logical reads"  → 500k rows × ~3 reads each = 1.5M logical reads (bad)
--   "CPU time"       → cursor loops spike CPU; set-based can parallelize

SET STATISTICS TIME, IO OFF;

3. Rewrite Pattern 1: Cursor → UPDATE with JOIN

The most common cursor pattern is iterating through rows and updating them one at a time based on a calculation. This is almost always replaceable with a single UPDATE ... FROM ... JOIN statement.

Before: Cursor-Based Row Update

-- Cursor: apply tiered discount based on order total
DECLARE @OrderID INT, @Total DECIMAL(10,2), @Discount DECIMAL(5,2);

DECLARE c CURSOR FAST_FORWARD FOR
    SELECT OrderID, TotalAmount FROM Sales.Orders WHERE Status = 'New';

OPEN c;
FETCH NEXT FROM c INTO @OrderID, @Total;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Discount = CASE
        WHEN @Total >= 10000 THEN 0.15
        WHEN @Total >= 5000  THEN 0.10
        WHEN @Total >= 1000  THEN 0.05
        ELSE 0.00
    END;

    UPDATE Sales.Orders
    SET    Discount = @Discount
    WHERE  OrderID  = @OrderID;

    FETCH NEXT FROM c INTO @OrderID, @Total;
END;
CLOSE c; DEALLOCATE c;

After: Single Set-Based UPDATE

-- Set-based: apply tiered discount in one statement
UPDATE Sales.Orders
SET    Discount = CASE
    WHEN TotalAmount >= 10000 THEN 0.15
    WHEN TotalAmount >= 5000  THEN 0.10
    WHEN TotalAmount >= 1000  THEN 0.05
    ELSE 0.00
END
WHERE  Status = 'New';

SQL Server can evaluate the CASE expression inline for every row in a single scan pass, with one lock escalation and one batch log write. For 500,000 rows this can be 100–500x faster than the cursor version.

Rewrite with a Derived Table (for complex logic)

-- When the new values come from another table or subquery
UPDATE o
SET    o.Discount    = d.DiscountRate,
       o.DiscountAmt = o.TotalAmount * d.DiscountRate
FROM   Sales.Orders       o
JOIN   Sales.DiscountTiers d
       ON  o.TotalAmount BETWEEN d.MinAmount AND d.MaxAmount
WHERE  o.Status = 'New';

4. Rewrite Pattern 2: WHILE Loop → Recursive CTE

WHILE loops are commonly used to generate sequences of numbers or dates. A recursive CTE expresses the same iteration declaratively and lets the optimizer decide how to materialise the set.

Before: WHILE Loop Number Generator

-- Generate numbers 1–10000 into a temp table
CREATE TABLE #Numbers (n INT);

DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO #Numbers (n) VALUES (@i);
    SET @i += 1;
END;

After: Recursive CTE

-- Set-based: recursive CTE number generator
WITH Numbers AS (
    SELECT 1 AS n                      -- anchor member
    UNION ALL
    SELECT n + 1                       -- recursive member
    FROM   Numbers
    WHERE  n < 10000
)
SELECT n INTO #Numbers
FROM   Numbers
OPTION (MAXRECURSION 10000);          -- override default 100-level limit

Date Range Generator (practical use case)

-- Generate every date in a calendar year
WITH Dates AS (
    SELECT CAST('2026-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d)
    FROM   Dates
    WHERE  d < '2026-12-31'
)
SELECT d AS CalendarDate,
       DATENAME(WEEKDAY, d) AS DayName,
       MONTH(d)             AS MonthNum
FROM   Dates
OPTION (MAXRECURSION 366);

5. Rewrite Pattern 3: Cursor Accumulation → Window Function

Running totals are a classic cursor use case: accumulate a sum as you walk through ordered rows. Window functions eliminate this pattern entirely with a frame-aware aggregate.

Before: Cursor Running Total

-- Running total of daily sales via cursor
DECLARE @RunningTotal DECIMAL(12,2) = 0;
DECLARE @SaleDate DATE, @DailyAmt DECIMAL(12,2);

CREATE TABLE #RunningTotals (SaleDate DATE, DailyAmt DECIMAL(12,2), RunningTotal DECIMAL(12,2));

DECLARE c CURSOR FAST_FORWARD FOR
    SELECT SaleDate, DailyAmount
    FROM   Sales.DailySummary
    ORDER  BY SaleDate;

OPEN c;
FETCH NEXT FROM c INTO @SaleDate, @DailyAmt;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal += @DailyAmt;
    INSERT INTO #RunningTotals VALUES (@SaleDate, @DailyAmt, @RunningTotal);
    FETCH NEXT FROM c INTO @SaleDate, @DailyAmt;
END;
CLOSE c; DEALLOCATE c;

After: Window Aggregate with ROWS Frame

-- Set-based: running total using SUM() OVER() with a ROWS frame
SELECT
    SaleDate,
    DailyAmount,
    SUM(DailyAmount) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Sales.DailySummary
ORDER BY SaleDate;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame tells SQL Server to sum all rows from the first row up to and including the current row within the defined order. This runs in a single pass, with an optional in-memory sort — no loop, no temp table.

Running Average Over Last 7 Days

SELECT
    SaleDate,
    DailyAmount,
    AVG(DailyAmount) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS Rolling7DayAvg
FROM Sales.DailySummary
ORDER BY SaleDate;

6. CROSS APPLY: Row-Level Subquery Execution

CROSS APPLY is one of SQL Server's most powerful set-based tools. It acts like an inner join but instead of joining two independent tables, it applies a table-valued expression (a subquery, inline TVF, or TVF call) once per row of the left table. Only rows from the left table that produce at least one row from the applied expression are included in the result — analogous to INNER JOIN.

Basic Syntax

SELECT  left_table.col1,
        applied.col2
FROM    left_table
CROSS APPLY (
    SELECT  col2
    FROM    right_table
    WHERE   right_table.fk = left_table.pk   -- reference to outer row
) AS applied;

The inner query can reference columns from left_table — something a regular subquery in the FROM clause cannot do (that requires a correlated subquery in SELECT or a lateral join, which SQL Server expresses as APPLY).

7. CROSS APPLY Use Case: Top N Rows Per Group

Returning the top N rows per group is a common problem. Window functions work well, but CROSS APPLY with TOP often produces a more efficient plan because SQL Server can apply the TOP filter deep in the inner query before joining back.

Top 3 Products Per Category

-- CROSS APPLY: top 3 most expensive products per category
SELECT  c.CategoryName,
        p.ProductName,
        p.ListPrice
FROM    Production.ProductCategory c
CROSS APPLY (
    SELECT   TOP 3
             ProductName,
             ListPrice
    FROM     Production.Product
    WHERE    CategoryID = c.CategoryID     -- references outer row
    ORDER BY ListPrice DESC
) AS p
ORDER BY c.CategoryName, p.ListPrice DESC;

Comparison: ROW_NUMBER() Alternative

-- Equivalent using ROW_NUMBER() — often slightly less efficient
WITH Ranked AS (
    SELECT  CategoryID,
            ProductName,
            ListPrice,
            ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ListPrice DESC) AS rn
    FROM    Production.Product
)
SELECT  c.CategoryName,
        r.ProductName,
        r.ListPrice
FROM    Production.ProductCategory c
JOIN    Ranked r ON r.CategoryID = c.CategoryID AND r.rn <= 3
ORDER BY c.CategoryName, r.ListPrice DESC;

Both are set-based and vastly superior to a cursor. Check execution plans on your data to decide which performs better — CROSS APPLY with TOP can leverage a non-clustered index on (CategoryID, ListPrice DESC) very effectively.

8. CROSS APPLY with STRING_SPLIT: Unnesting Delimited Values

STRING_SPLIT (SQL Server 2016+) is a table-valued function that splits a delimited string into a single-column result set. Combined with CROSS APPLY, it allows you to normalise denormalised, comma-delimited data in a pure set-based operation.

Splitting Tags Per Article

-- Articles table with comma-delimited tags column
-- ArticleID | Title          | Tags
-- 1         | SQL Indexes    | sql,performance,index
-- 2         | Docker Basics  | docker,containers,devops

SELECT  a.ArticleID,
        a.Title,
        LTRIM(RTRIM(tag.value)) AS Tag
FROM    dbo.Articles a
CROSS APPLY STRING_SPLIT(a.Tags, ',') AS tag
WHERE   a.Tags IS NOT NULL
ORDER BY a.ArticleID;

This replaces a cursor that would loop through each article, split its tag string, and insert rows one at a time into a staging table.

Filter Rows Containing a Specific Tag

-- Find all articles tagged 'performance'
SELECT  a.ArticleID,
        a.Title
FROM    dbo.Articles a
CROSS APPLY STRING_SPLIT(a.Tags, ',') AS tag
WHERE   LTRIM(RTRIM(tag.value)) = 'performance';

9. OUTER APPLY: Preserving Rows With No Match

OUTER APPLY is to CROSS APPLY what LEFT JOIN is to INNER JOIN. When the applied table-valued expression returns zero rows for a given outer row, OUTER APPLY still includes the outer row in the result — with NULLs for all columns from the applied expression.

Basic Syntax

SELECT  left_table.col1,
        applied.col2    -- NULL if no rows returned by applied expression
FROM    left_table
OUTER APPLY (
    SELECT  TOP 1 col2
    FROM    right_table
    WHERE   right_table.fk = left_table.pk
    ORDER BY right_table.created_date DESC
) AS applied;

Use OUTER APPLY when you want every row from the left table regardless of whether the inner expression produces a match — such as returning all customers even if they have no orders.

10. OUTER APPLY Use Case: Latest Order Per Customer

Fetching the most recent order for every customer — including customers who have never ordered — is a textbook OUTER APPLY scenario.

-- Latest order details for every customer (including those with no orders)
SELECT
    c.CustomerID,
    c.CompanyName,
    c.Email,
    lo.OrderID,
    lo.OrderDate,
    lo.TotalAmount,
    lo.Status
FROM  Sales.Customers c
OUTER APPLY (
    SELECT  TOP 1
            OrderID,
            OrderDate,
            TotalAmount,
            Status
    FROM    Sales.Orders
    WHERE   CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS lo
ORDER BY c.CompanyName;

Customers with no orders appear with NULLs for all lo.* columns. The alternative — a correlated subquery for each column of the order — would require four separate subqueries and be far less maintainable.

Equivalent LEFT JOIN with ROW_NUMBER()

-- Alternative approach using window function
WITH LatestOrders AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM    Sales.Orders
)
SELECT  c.CustomerID,
        c.CompanyName,
        lo.OrderID,
        lo.OrderDate,
        lo.TotalAmount
FROM    Sales.Customers  c
LEFT JOIN LatestOrders   lo ON lo.CustomerID = c.CustomerID AND lo.rn = 1
ORDER BY c.CompanyName;

Both are correct. OUTER APPLY is often cleaner and can be more efficient when the inner query is complex and benefits from an index seek per outer row.

11. The Quirky Update Anti-Pattern

The "quirky update" is a T-SQL hack that uses a variable assignment inside an UPDATE to simulate a running total without a cursor:

-- ANTI-PATTERN: quirky update — DO NOT USE IN PRODUCTION
DECLARE @RunningTotal DECIMAL(12,2) = 0;

UPDATE  Sales.DailySummary
SET     @RunningTotal = RunningTotal = @RunningTotal + DailyAmount
-- No ORDER BY guaranteed — row processing order is undefined!

This pattern is undocumented and unreliable. Microsoft has never guaranteed the row processing order of an UPDATE statement. In practice:

• Row order is non-deterministic and can change with query plan changes.
• It breaks silently under parallelism — a parallel UPDATE plan can process rows in any order across threads.
• It was accidentally removed as a documented technique starting with SQL Server 2012's optimizer improvements.

The Correct Replacement

-- Correct: use SUM() OVER() window aggregate
SELECT
    SaleDate,
    DailyAmount,
    SUM(DailyAmount) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Sales.DailySummary
ORDER BY SaleDate;

12. Bulk INSERT/UPDATE Patterns: MERGE vs Row-by-Row

Loading or synchronising large datasets is another area where set-based operations provide dramatic gains. The MERGE statement combines INSERT, UPDATE, and DELETE into a single set-based operation.

Row-by-Row Approach (avoid)

-- Anti-pattern: row-by-row upsert in a WHILE loop
WHILE EXISTS (SELECT 1 FROM #StagingData WHERE Processed = 0)
BEGIN
    DECLARE @ID INT, @Val NVARCHAR(100);
    SELECT TOP 1 @ID = ID, @Val = Value FROM #StagingData WHERE Processed = 0;

    IF EXISTS (SELECT 1 FROM dbo.Target WHERE ID = @ID)
        UPDATE dbo.Target SET Value = @Val WHERE ID = @ID;
    ELSE
        INSERT INTO dbo.Target (ID, Value) VALUES (@ID, @Val);

    UPDATE #StagingData SET Processed = 1 WHERE ID = @ID;
END;

Set-Based MERGE (recommended)

-- Set-based: MERGE handles INSERT + UPDATE + DELETE atomically
MERGE dbo.Target AS tgt
USING #StagingData  AS src ON tgt.ID = src.ID
WHEN MATCHED AND tgt.Value <> src.Value THEN
    UPDATE SET tgt.Value = src.Value,
               tgt.ModifiedDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value, CreatedDate)
    VALUES (src.ID, src.Value, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;                             -- remove rows not in staging

Benchmark Comparison (conceptual)

13. When Cursors Are Acceptable

Set-based is the goal, but there are genuine use cases where a cursor or WHILE loop is the right tool:

• Sequential dependency across rows — when row N's output is the input to row N+1 and the dependency cannot be expressed in a window frame (rare but real in certain financial ledger calculations).
• Dynamic SQL generation — building and executing a series of ALTER TABLE, CREATE INDEX, or EXEC sp_rename calls across a list of objects fetched from a catalog query. Each DDL statement must execute individually; there is no set-based DDL.
• External API or linked server calls per row — calling EXEC xp_cmdshell, sp_OAMethod, or a linked server stored procedure for each row inherently requires iteration.
• Administrative scripts — backup/restore loops, kill-session loops, database-by-database maintenance — these are inherently sequential and low-frequency enough that performance is irrelevant.
• Very small sets with complex logic — iterating over 5–10 configuration rows to call a procedure is perfectly fine. The overhead is negligible; optimise for readability.

14. Benchmarking: Measuring Cursor vs Set-Based Performance

Before optimising, measure. SQL Server provides two commands that give precise I/O and CPU metrics without requiring external tools.

SET STATISTICS TIME IO ON

-- Enable both time and I/O statistics
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- ── Run your cursor-based code here ──
-- ... (your cursor block)

-- ── Then reset and run set-based version ──
-- ... (your set-based query)

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

In the Messages tab of SSMS you will see output like:

-- Cursor version (500k rows):
Table 'Orders'. Scan count 500000, logical reads 1500000
SQL Server Execution Times: CPU time = 14230 ms,  elapsed time = 18440 ms.

-- Set-based version (same 500k rows):
Table 'Orders'. Scan count 1, logical reads 3842
SQL Server Execution Times: CPU time = 312 ms,  elapsed time = 891 ms.

Reading the Output

Using sys.dm_exec_query_stats for Historical Comparison

-- Find the most CPU-intensive queries in cache
SELECT TOP 10
    qs.total_worker_time / qs.execution_count   AS avg_cpu_us,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END
         - qs.statement_start_offset)/2)+1)     AS query_text
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_us DESC;

Read Next