Microsoft Docs

Cursor (Transact‑SQL)

A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row. While set‑based operations are preferred for performance, cursors are useful when row‑by‑row processing is required.

Table of Contents

Syntax

DECLARE cursor_name CURSOR
    [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
FOR
    select_statement
[ FOR UPDATE OF column_name [ ,...n ] ];

Example

This example demonstrates a simple forward‑only, read‑only cursor that iterates through employee records and prints each name.

USE AdventureWorks2019;
GO

DECLARE @EmployeeID INT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50);

DECLARE emp_cursor CURSOR FAST_FORWARD FOR
SELECT BusinessEntityID, FirstName, LastName
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
    ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY BusinessEntityID;

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @EmployeeID, @FirstName, @LastName;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT('ID: ', @EmployeeID, ' – ', @FirstName, ' ', @LastName);
    FETCH NEXT FROM emp_cursor INTO @EmployeeID, @FirstName, @LastName;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Cursor Options

OptionDescription
LOCAL / GLOBALScope of the cursor (default is GLOBAL).
FORWARD_ONLYOnly forward movement; higher performance.
SCROLLAllows backward movement.
STATICResult set is a snapshot; does not reflect changes.
KEYSETKeys are fixed; data reflects changes to non‑key columns.
DYNAMICFully dynamic; reflects all changes.
FAST_FORWARDRead‑only, forward‑only, optimised for speed.
READ_ONLYPrevents updates through the cursor.
SCROLL_LOCKSLocks rows as they are fetched.
OPTIMISTICUses optimistic concurrency for updates.

Performance Considerations