Cursor (FORWARD_ONLY, FAST_FORWARD)
CREATE TABLE #temp (k1 INT IDENTITY, c1 int)
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
SELECT * FROM #temp
– FORWARD_ONLY – default cursor returs rows sequentially and doesn’t require space on tempdb and changes to data is visible right away
– The cursor is not scrollable
– DECLARE c CURSOR FORWARD_ONLY FAST_FORWARD FOR SELECT k1, c1 FROM #temp
DECLARE c CURSOR FOR SELECT k1, c1 FROM #temp
OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
SELECT * FROM #temp
CLOSE c
DEALLOCATE c
DROP TABLE #temp
Output:
k1 c1
———– ———–
1 NULL
k1 c1
———– ———–
2 2
k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL