Cursor Options That Matter: LOCAL, FAST_FORWARD and STATIC

Some tasks still need one procedure call per row, so a cursor can be the right tool. The cursor options matter: LOCAL FAST_FORWARD gives a simple forward-only read, while STATIC takes a snapshot in tempdb. Measure both against an unqualified declaration before copying a default into another job.

First Ask Whether a Set Works

An UPDATE or INSERT that handles all rows at once is usually simpler than a loop. Cursor work is defensible when each row needs a stored procedure with side effects, a sequential external action, or an API that accepts one item at a time. Keep the reason explicit. A cursor chosen only because the developer started with a WHILE loop should be reconsidered.

I have used a row-by-row job for a vendor procedure that accepted one account ID per call. The row set was small, and the procedure contract mattered more than a theoretical set rewrite. How many rows does your job process at peak, and can the per-row work be batched safely?

Build the Same Input for All Three Tests

Use a local temp table with a stable key and several thousand rows. Run each cursor in the same session against that table. Do not include a real external procedure in an initial timing test; network and business work can overwhelm the cursor overhead. The loop below adds IDs into a variable so every row has a tiny observable operation.

DROP TABLE IF EXISTS #CursorWork;
CREATE TABLE #CursorWork (ID int NOT NULL PRIMARY KEY);
;WITH n AS
(
    SELECT TOP (10000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT #CursorWork (ID) SELECT ID FROM n;
SELECT COUNT(*) AS work_rows FROM #CursorWork;

The source catalog only supplies enough rows for a test; the temp table is the stable input. Repeat the timing several times in different orders because cache warmth and server load affect milliseconds. Record row count and SQL Server version with the result.

Time LOCAL FAST_FORWARD

FAST_FORWARD is forward-only and read-only, which matches a one-pass job. LOCAL limits cursor scope to the current batch or procedure unless explicitly returned. Capture start and end times, then verify the sum so an accidentally skipped row does not look fast. The example is a reusable declaration pattern.

DECLARE @id int, @sum bigint = 0,
        @started datetime2(3) = SYSDATETIME();
DECLARE work_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT ID FROM #CursorWork ORDER BY ID;
OPEN work_cursor;
FETCH NEXT FROM work_cursor INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sum += @id;
    FETCH NEXT FROM work_cursor INTO @id;
END;
CLOSE work_cursor;
DEALLOCATE work_cursor;
SELECT N'LOCAL FAST_FORWARD' AS option_name, @sum AS checksum,
       DATEDIFF_BIG(millisecond, @started, SYSDATETIME()) AS elapsed_ms;

In a procedure, place error handling around the per-row call and close and deallocate the cursor on both success and failure. Do not leave a global cursor open for the next connection or job run to discover. A forward-only read also makes it clear that the loop is not editing the cursor result set.

Compare the STATIC and Default Cursor Options

Run the same body after changing only the declaration to CURSOR LOCAL STATIC and then to plain CURSOR. STATIC materializes a snapshot of the cursor's rows in tempdb when opened, so its open cost and tempdb writes can matter for a large result. It can be useful when a stable view of the input is required while base rows change.

-- Test A: replace the prior declaration only.
DECLARE work_cursor CURSOR LOCAL STATIC FOR
    SELECT ID FROM #CursorWork ORDER BY ID;
-- Test B in a separate run:
-- DECLARE work_cursor CURSOR FOR
--     SELECT ID FROM #CursorWork ORDER BY ID;

Use the full open, fetch, close, and deallocate block from the previous test for each declaration. Plain CURSOR behavior depends on database cursor-default settings and the SELECT shape; do not assume it always means one specific implementation. Query the database's cursor_default setting and inspect actual cursor properties when the result surprises you. An unqualified declaration can create a broader lifetime than intended.

Watch tempdb During STATIC

Measure tempdb internal-object allocation before and after the STATIC test in a dedicated session. sys.dm_db_session_space_usage reports pages allocated and deallocated for the session. It is a useful approximation, not a perfect invoice for one cursor when other work occurs in that session. Capture it around each test and compare deltas. The session view adds a batch's pages only after that batch ends, so run this query as its own batch. While the cursor is still open, sys.dm_db_task_space_usage shows the live figure.

SELECT session_id, internal_objects_alloc_page_count,
       internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;

A STATIC cursor can be faster during repeated fetches because its snapshot is materialized, but the initial copy and tempdb cost must be included. FAST_FORWARD can choose a plan suited to one pass. The fastest option depends on query shape, rows, and what the loop does. Do not publish a universal winner from a ten-row test.

Keep Cursor Options in a Small, Safe Template

For a one-pass read-only job, start with DECLARE … CURSOR LOCAL FAST_FORWARD FOR SELECT … ORDER BY …. Make the ordering explicit if the procedure calls must occur in sequence. If a stable snapshot is required, test STATIC and budget for tempdb. Close and deallocate in a reliable cleanup path. Batch commits where business rules allow so a late error does not undo a huge transaction.

I keep the timed three-way comparison beside the job's expected row count. After a data growth event, I rerun it. A cursor can be a practical integration tool when its row count and side effects are bounded, measured, and written down. Cursor options are part of that contract, not decoration on the declaration.

Interpret Timings for Cursor Options Honestly

The example's accumulator keeps the loop body deliberately cheap, which makes cursor overhead visible. A real job calling a procedure that performs I/O or waits on a remote service can spend nearly all its time outside the cursor. Measure total elapsed time, procedure time, CPU, logical reads, and tempdb allocation in the production-shaped rehearsal. A faster fetch method can be irrelevant when each call takes a second.

Use the same ordered SELECT, same row count, and same loop body for all three trials. Run A, B, C and then C, B, A to reduce cache-order bias. Report the median rather than choosing the fastest single run. SQL Server can convert a default cursor when the SELECT conflicts with the requested cursor options. Inspect messages and actual properties if the observed behavior differs from the declaration.

Bound the Work

A cursor that processes a growing backlog needs a restart key and a limit per run. Select a stable set of IDs, mark each completed item, and make the called procedure safe to retry. Do not hold one transaction open over ten thousand independent calls unless atomicity is genuinely required. The transaction log and locks can become the real performance problem. A local cursor with explicit cleanup is a small part of a reliable batch design.

Related reading on this blog: Convert Cursor to Set Based Insert and Replacing a Cursor with a Common Table Expression.

A cursor option is not a style choice, it is a behavior to test across the full loop.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.