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

Help and Information

Getting SQL Server 2005 Assistance