API Server Cursors
The OLE DB, ODBC, and ADO APIs support mapping cursors over the result sets of executed SQL statements. The Microsoft SQL Server Native Client OLE DB provider and SQL Server Native Client ODBC driver implement these operations through the use of API server cursors. API server cursors are cursors implemented on the server and managed by API cursor functions. As the application calls the API cursor functions, the cursor operation is transmitted to the server by the OLE DB provider or ODBC driver.
When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to:
Open a connection.
Set attributes or properties defining the characteristics of the cursor the API automatically maps over each result set.
Execute one or more Transact-SQL statements.
Use API functions or methods to fetch the rows in the result sets.
When the API cursor attributes or properties are set to their default settings, the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver use default result sets. Although the API is technically asking for a cursor, the default cursor characteristics match the behavior of a default result set. The OLE DB provider and ODBC driver, therefore, implement the default cursor options using a default result set because it is the most efficient way to retrieve rows from the server. When using default result sets, an application can execute any Transact-SQL statement or batch, but it can only have one outstanding statement on a connection. This means the application must process or cancel all the result sets returned by one statement before it can execute another statement on the connection.
When the API cursor attributes or properties are set to anything other than their defaults, the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.
API Server Cursor Restrictions
An application cannot execute the following statements when using API server cursors:
Transact-SQL statements that SQL Server does not support in server cursors.
Batches or stored procedures that return multiple result sets.
SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses.
An EXECUTE statement referencing a remote stored procedure.
API Server Cursor Implementation
The SQL Server Native Client OLE DB provider and SQL Server Native Client ODBC driver use these special system stored procedures to signal cursor operations to the server:
sp_cursoropen defines the SQL statement to be associated with the cursor and the cursor options, then populates the cursor.
sp_cursorfetch fetches a row or block of rows from the cursor.
sp_cursorclose closes and deallocates the cursor.
sp_cursoroption is used to set various cursor options.
sp_cursor is used to request positioned updates.
sp_cursorprepare compiles the Transact-SQL statement or batch associated with a cursor into an execution plan but does not create the cursor.
sp_cursorexecute creates and populates a cursor from the execution plan created by sp_cursorprepare.
sp_cursorunprepare discards the execution plan from sp_cursorprepare.
sp_cursorprepexec compiles a plan for the submitted Transact-SQL statement or batch associated with a cursor, creates the cursor, and populates it. sp_cursorprepexec combines the behavior of sp_cursorprepare and sp_cursorexecute.
These system stored procedures will show up in SQL Server Profiler traces of ADO, OLE DB, and ODBC applications that are using API server cursors. They are intended only for the internal use of the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.
When SQL Server executes a statement for a connection, no other statements can be executed on the connection until all the results from the first statement have been processed or canceled. This rule still holds when using API server cursors, but to the application it looks like SQL Server has started supporting multiple active statements on a connection. This is because the full result set is stored in the server cursor and the only statements being transmitted to SQL Server are the executions of the sp_cursor system stored procedures. SQL Server executes the stored procedure, and as soon as the client retrieves the result set it can execute any other statement. The OLE DB provider and ODBC driver always retrieve all the results from an sp_cursor stored procedure before they return control to the application. This lets applications interleave fetches against multiple active server cursors.
This table shows how an application can process two cursors at the same time on a connection using two statement handles.
Statement handle 1 |
Statement handle 2 |
|---|---|
Set cursor attributes such that an API server cursor will be used. |
|
SQLExecDirect an SQL statement. The ODBC driver calls sp_cursoropen and retrieves the result set returned by the procedure. |
|
|
Set cursor attributes such that an API server cursor will be used. |
|
SQLExecDirect an SQL statement. The ODBC driver calls sp_cursoropen and retrieves the result set returned by the procedure. |
SQLFetchScroll to retrieve the first block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. |
|
|
SQLFetchScroll to retrieve the first block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. |
SQLFetchScroll to retrieve another block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. |
|
|
SQLFetchScroll to retrieve another block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. |
Call SQLFreeStmt or SQLCloseCursor. The driver calls sp_cursorclose. |
|
|
Call SQLFreeStmt or SQLCloseCursor. The driver calls sp_cursorclose. |
Because no results are left outstanding on the connection after any call to an sp_cursor stored procedure, you can execute multiple Transact-SQL statements concurrently on a single connection, provided they are all executed with API server cursors.
Specifying API Server Cursors
Here is a summary of how API server cursors are used in the APIs:
OLE DB
Open a session object, open a command object, and specify the command text.
Set rowset properties such as DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_OWNUDPATEDELETE to control cursor behaviors.
Execute the command object.
Fetch the rows in the result set using such methods as IRowset::GetNextRows, IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsAtBookmark, and IRowsetScroll::GetRowsAtRatio.
ODBC
Open a connection and call SQLAllocHandle to allocate statement handles.
Call SQLSetStmtAttr to set the SQL_ATTR_CURSOR_TYPE, SQL_ATTR_CONCURRENCY, and SQL_ATTR_ROW_ARRAY_SIZE attributes. Alternatively, you can specify cursor behaviors by setting the attributes SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY.
Execute a Transact-SQL statement using either SQLExecDirect or SQLPrepare and SQLExecute.
Fetch rows or blocks of rows using SQLFetch or SQLFetchScroll.
ADO
Define a Connection object and a Recordset object, and then execute the Open method on the Connection object.
Execute the Open method on the Recordset object specifying a CursorType and/or a LockType parameter.
Fetch rows using the Move, MoveFirst, MoveLast, MoveNext, and MovePrevious recordset methods.
API Server Cursors and SET Options
In SQL Server, if a fetch statement is issued and there is a change to any of the following plan-affecting options or options required for indexed views or computed columns, the cursor uses a snapshot of the option values in effect at the time the cursor is opened. These values are used for all subsequent fetch operations, and changes in the current context are ignored.
Plan-affecting options |
ARITHABORT NUMERIC_ROUNDABORT FORCEPLAN QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON ANSI_WARNINGS ANSI_PADDING ANSI_NULLS CONCAT_NULL_YIELDS_NULL DATEFIRST DATEFORMAT LANGUAGE TEXTSIZE |
Indexed views and computed columns |
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT (under compatibility level of 80 or lower) CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER NUMERIC_ROUNDABORT |