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, you should first 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 Microsoft SQL Server default result set. The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider 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 default of the Microsoft SQL Server Native Client OLE DB Provider and SQL Server Native Client ODBC driver 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. OLE DB does not have its own implementation of client cursors, but the ADO API does.

The following table 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.