Cursor Behaviors


Updated: October 24, 2016

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

ODBC supports the ISO options for specifying the behavior of cursors by specifying their scrollability and sensitivity. These behaviors are specified by setting the SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY options on a call to SQLSetStmtAttr. The SQL Server Native Client ODBC driver implements these options by requesting server cursors with the following characteristics.

Cursor behavior settingsServer cursor characteristics requested
SQL_SCROLLABLE and SQL_SENSITIVEKeyset-driven cursor and version-based optimistic concurrency
SQL_SCROLLABLE and SQL_INSENSITIVEStatic cursor and read-only concurrency
SQL_SCROLLABLE and SQL_UNSPECIFIEDStatic cursor and read-only concurrency
SQL_NONSCROLLABLE and SQL_SENSITIVEForward-only cursor and version-based optimistic concurrency
SQL_NONSCROLLABLE and SQL_INSENSITIVEDefault result set (forward-only, read-only)
SQL_NONSCROLLABLE and SQL_UNSPECIFIEDDefault result set (forward-only, read-only)

Version-based optimistic concurrency requires a timestamp column in the underlying table. If version-based optimistic concurrency control is requested on a table that does not have a timestamp column, the server uses values-based optimistic concurrency.

When SQL_ATTR_CURSOR_SCROLLABLE is set to SQL_SCROLLABLE, the cursor supports all the different values for the FetchOrientation parameter of SQLFetchScroll. When SQL_ATTR_CURSOR_SCROLLABLE is set to SQL_NONSCROLLABLE, the cursor only supports a FetchOrientation value of SQL_FETCH_NEXT.

When SQL_ATTR_CURSOR_SENSITIVITY is set to SQL_SENSITIVE, the cursor reflects data modifications made by the current user or committed by other users. When SQL_ATTR_CURSOR_SENSITIVITY is set to SQL_INSENSITIVE, the cursor does not reflect data modifications.

Using Cursors (ODBC)Cursor Properties

Community Additions