Cursor Types (Database Engine)
ODBC and ADO define four cursor types supported by Microsoft SQL Server 2005. 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
Other Resources
DECLARE CURSOR (Transact-SQL)
Rowsets and SQL Server Cursors
How Cursors Are Implemented