Controlling Cursor Behavior

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

Cursor types

The data access 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 ISO standard defines the DECLARE CURSOR keywords SCROLL and INSENSITIVE to specify the behavior of cursors. Some data access APIs also support defining cursor behavior in terms of scrollability and sensitivity.

  • ADO supports 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.

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

  • The SqlClient 2.0 managed client does not expose server side cursors.

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 and greater, DECLARE CURSOR was extended to support cursor-type keywords.

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.

Community Additions