Controlling Cursor Behavior

There are two models for specifying the behavior of a cursor:

Cursor types

The database APIs usually specify the behavior of cursors by dividing them into four cursor types: forward-only, static (sometimes called snapshot or insensitive), keyset-driven, and dynamic.

Cursor behaviors

The SQL-92 standard defines the DECLARE CURSOR keywords SCROLL and INSENSITIVE to specify the behavior of cursors. Some database APIs also support defining cursor behavior in terms of scrollability and sensitivity.

ADO and DB-Library support specifying only cursor types, not cursor behaviors.

ODBC supports specifying cursor behavior using either the cursor types or the cursor behaviors of scrollability and insensitivity.

Prior to Microsoft® SQL Server™ version 7.0, the DECLARE CURSOR statement used to define Transact-SQL cursors supported only cursor behaviors of SCROLL and INSENSITIVE. In SQL Server 7.0, DECLARE CURSOR has been extended to support cursor-type keywords.

OLE DB's cursor behavior model differs from both cursor behaviors and cursor types.

Do not specify both cursor types and cursor behaviors for a cursor. Use one or the other. Because ODBC and Transact-SQL cursors support both cursor behaviors and cursor types, use either ODBC or Transact-SQL when defining the cursor. The ODBC specification states that specifying both cursor behaviors and cursor types can lead to unpredictable results.