Specifying Cursors

In the OLE DB, ODBC, and ADO specifications, a cursor is implicitly opened over any result set returned by a Transact-SQL statement. Before executing a Transact-SQL statement, set attributes or properties to define the characteristics of the cursor. You can then call API functions to fetch one row or batch of rows at a time. The default settings for the API cursor attributes or properties have the same characteristics as a SQL Server default result set. The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server both implement default result sets when the cursor attributes or properties are set to their defaults. No cursor is used in this case. The driver and provider only implement a cursor if any of the cursor attributes or properties are changed from their defaults.

OLE DB, ODBC, and ADO each use different terms in referring to this combination of a result set and cursor:

• OLE DB uses the term rowset to refer to the combination of a result set and its associated cursor behaviors.
• ODBC uses the terms result set and cursor somewhat interchangeably because it considers each result set to have a cursor automatically mapped over it. A rowset in ODBC is specifically the number of rows returned on a fetch.
• ADO uses the term recordset in the same way that OLE DB uses the term rowset.

The DB-Library API supports two ways to process result sets. The DB-Library core functions support processing the results of a Transact-SQL statement only as a default result set. The core functions allow you only to retrieve the rows of a result set one at a time in a forward-only direction, and do not support making updates through the result set. If you want to use cursor processing in a DB-Library application, you must use the special functions from the DB-Library Cursor Library instead of the core functions.

The default of the Microsoft OLE DB Provider for SQL Server, SQL Server ODBC driver, and DB-Library is to use an API server cursor to implement any requested cursor functions. ODBC client applications can use client cursors instead of server cursors by loading the ODBC Cursor Library. DB-Library applications can use client cursors by setting the DBCLIENTCURSOR option. OLE DB does not have its own implementation of client cursors, but the ADO API does.

The following illustration summarizes what type of cursor or result set is implemented based on the current API cursor settings and the Transact-SQL statement being executed. The cells with “Do not combine cursor types” indicate that both a Transact-SQL server cursor and an API cursor are being requested at the same time. This can result in undefined behavior and should not be done.