Dynamic Cursors (Database Engine)

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.

Note

If the execution plan chosen to execute a query for a dynamic cursor is using a heap scan, and conditions are such that a page or table lock is acquired, deleting a row may result in the whole page being deallocated. In this case, the markers used by dynamic cursors for positioning may become invalid and a subsequent fetch from the cursor may fail with error 16931. Possible solutions include creating a clustered index on the table, using a different cursor type, or evaluating whether it is possible to prevent page and table-level locks.

Note

In SQL Server 2005, dynamic cursor worktable updates are always in place. That is, even if key columns are changed as part of the update, the current row is refreshed. In SQL Server 2000, the current row was marked as deleted (as it would have been for not-in-place keyset cursors), but the row was not inserted at the end of the worktable (as it was for keyset cursors). The result was that the cursor refresh failed to find the row and reported it missing. SQL Server 2005 keeps the cursor worktable in sync, and the refresh is able to find the row because it has the new keys.