This documentation is archived and is not being maintained.

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.