SQL Server Cursor Types and Options
Objective: Choose cursor behavior by scrolling, updateability, and visibility requirements.
Do not choose a cursor by a fixed ranking of memory consumption. Result size, indexes, query plan, worktables, concurrency, and the work inside the loop all matter. Choose required behavior first, then measure a representative workload.
Four common choices
| Choice | Behavior | Typical reason to choose it |
|---|---|---|
| STATIC | Uses a copy of the result in tempdb; changes to source rows after opening do not appear in that copy. Read-only. | A stable result for repeated navigation. |
| KEYSET | Retains keys defining membership and order; fetched values can reflect changes to existing rows. Deleted members can produce a missing-row fetch. | Stable membership with visibility of value changes. |
| DYNAMIC | Membership, ordering, and values can change as rows are fetched, subject to visibility and isolation rules. | A result that must reflect changes during navigation. |
| FAST_FORWARD | An optimized forward-only, read-only option. It is not a promise of snapshot semantics. | A single forward pass without positioned updates. |
Options describe different properties
LOCAL and GLOBAL control name scope. FORWARD_ONLY and SCROLL control navigation. READ_ONLY controls updates through the cursor. Read-only does not mean forward-only: a STATIC SCROLL cursor can move backward while remaining read-only.
For a simple read-only pass, explicitly request LOCAL FAST_FORWARD. For a stable scrolling demonstration, use LOCAL SCROLL STATIC READ_ONLY. Do not combine FAST_FORWARD with SCROLL or FOR UPDATE.
Requested versus actual behavior
Some SELECT statements cannot support every requested cursor property. SQL Server can convert the cursor type. The TYPE_WARNING option requests a warning when an implicit conversion occurs. Inspect actual behavior with tools such as sp_describe_cursor or sys.dm_exec_cursors(@@SPID) when diagnosing a cursor.
Do not treat FAST_FORWARD as a synonym for STATIC. If a stable copy is part of the requirement, request the appropriate cursor explicitly and review the isolation requirements. Lesson 11 examines change visibility in more detail.