skip to content »

Fetching and updating cursors

In cases where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index, the modified value is visible through the cursor.

fetching and updating cursors-21fetching and updating cursors-38

No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor. Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.Keyset The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened.Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset.These applications need a mechanism to work with one row or a small block of rows at a time.Cursors are an extension to result sets that provide that mechanism.Transact-SQL uses the term insensitive for static cursors.

Some database APIs identify them as snapshot cursors.

The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.

Because the cursor cannot be scrolled backward, most changes made to rows in the database after the row was fetched are not visible through the cursor.

The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable.

When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.

Client cursors are implemented by caching all the result set rows on the client.