How SQL Server Cursors Respond to Data Changes
Objective: Predict change visibility without confusing membership, values, and isolation.
Ask three different questions: can existing row values change, can rows enter or leave the result, and can their order change? Membership means which rows belong to the cursor result. The cursor type and transaction isolation both affect what a fetch observes.
Compare change behavior
| Cursor choice | After opening |
|---|---|
| STATIC | Reads its stored result copy. Subsequent source changes do not refresh that copy. |
| KEYSET | Keeps the selected key membership and order. Values for existing keys can change; a deleted member can give @@FETCH_STATUS = -2. External inserts do not join the existing keyset. |
| DYNAMIC | Can reflect changes in values, order, and membership as fetching proceeds. Visibility still depends on transaction rules. |
| FAST_FORWARD | Specifies forward-only, read-only processing, not a fixed snapshot guarantee. Do not use it as a contract for stable change visibility. |
Observe a STATIC copy
Run this standalone example in a new query session. Expected results show the cursor's original value alongside the later value in the source table.
CREATE TABLE #CursorChangeDemo (ID int PRIMARY KEY, Label nvarchar(20));
INSERT INTO #CursorChangeDemo VALUES (1, N'Before');
DECLARE change_demo CURSOR LOCAL SCROLL STATIC READ_ONLY
FOR SELECT ID, Label FROM #CursorChangeDemo ORDER BY ID;
OPEN change_demo;
UPDATE #CursorChangeDemo SET Label = N'After' WHERE ID = 1;
FETCH FIRST FROM change_demo; -- expected: 1, Before
SELECT ID, Label FROM #CursorChangeDemo; -- expected: 1, After
CLOSE change_demo;
DEALLOCATE change_demo;
DROP TABLE #CursorChangeDemo;Keysets do not ignore all outside updates
Keyset cursors can detect other sessions' value changes, subject to visibility rules. What stays fixed is the keyset membership and ordering. A changed key can behave like deletion of the old member plus insertion of a new row.
Do not confuse read-only with isolation
READ_ONLY prevents positioned updates through that cursor. It does not prohibit other sessions from updating the source and does not by itself provide a transaction snapshot. Likewise, seeing a dynamic cursor does not mean uncommitted data is always visible.
Measure the real workload
STATIC results and keysets consume tempdb resources. Dynamic processing and repeated per-row procedure calls have their own costs. Check elapsed time, logical reads, blocking, and result correctness with realistic data instead of asserting that one cursor type always uses the least memory.
A concurrency test needs at least two database sessions, deliberate transaction boundaries, and a recorded isolation level. The single-session example above demonstrates a STATIC copy; it is not a complete concurrency benchmark.