Cursor Types (Database Engine)
ODBC and ADO define four cursor types supported by Microsoft SQL Server. The DECLARE CURSOR statement has been extended; thus you can specify the four cursor types for Transact-SQL cursors. These cursors vary in their ability to detect changes to the result set and in the resources, such as memory and space in tempdb, they consume. A cursor can detect changes to rows only when it attempts to fetch those rows a second time. There is no way for the data source to notify the cursor of changes to the currently fetched rows. The ability of a cursor to detect changes is also influenced by the transaction isolation level.
The four API server cursor types supported by SQL Server are:
Static cursors
Dynamic cursors
Forward-only cursors
Keyset-driven cursors
Static cursors detect few or no changes, but consume relatively few resources while scrolling. Dynamic cursors detect all changes, but consume more resources while scrolling. Keyset-driven cursors lie in between, detecting most changes, but with less resource demands than dynamic cursors.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.
See Also