Cursor and Its Type in SQL Server

A cursor within T-SQL is an in-memory representation of data pulled from a SELECT
statement. Cursors are actually an extension of a result set that provides the ability to
work with one row, or a small block of rows, at a time.

STATIC Think of this as a snapshot of the result set. A static cursor does not
reflect any types of changes to the underlying database, such as INSERTs, UP-
DATEs, or DELETEs. This is a read-only cursor that can go forward or backward.

FAST_FORWARD This will create a fast forward-only, read-only cursor. As the
name implies, the cursor can only go from the first row forward to the last row.
Unlike a STATIC cursor, rows are not retrieved from the database until they are
fetched. Any changes to a row (from INSERT, UPDATE or DELETE statements)
before the row is fetched, will be reflected in the fetched row.

DYNAMIC All changes made to the underlying data via INSERT, UPDATE, or
DELETE statements will be visible in the cursor. By default, uncommitted data is
not shown unless the transaction isolation level is set to read uncommitted.
KEYSET This creates a fixed order of rows in a keyset-driven cursor. The keyset
is built in the tempdb when the cursor is opened. While the order of the rows
can not change, any UPDATEs to the underlying data are reflected in the cursor.
INSERTs to the underlying data are not visible unless the cursor is closed and
reopened. If a row has been deleted the @@FETCH_STAUS will return a-2 for a
“row missing” status.

No comments: