• Article

Cursor Implementations

Microsoft SQL Server supports three cursor implementations:

  • Transact-SQL cursors
    Are based on the DECLARE CURSOR syntax and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They may also be contained in batches, stored procedures, or triggers.

  • Application programming interface (API) server cursors
    Support the API cursor functions in OLE DB and ODBC. API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server Native Client OLE DB provider or ODBC driver transmits the request to the server for action against the API server cursor.

  • Client cursors
    Are implemented internally by the SQL Server Native Client ODBC driver and by the DLL that implements the ADO API. Client cursors are implemented by caching all the result set rows on the client. Each time a client application calls an API cursor function, the SQL Server Native Client ODBC driver or the ADO DLL performs the cursor operation on the result set rows cached on the client.

Because Transact-SQL cursors and API server cursors are implemented on the server, they are referred to collectively as server cursors.

Do not mix the use of these various types of cursors. If you execute a DECLARE CURSOR and OPEN statement from an application, first set the API cursor attributes to their defaults. If you set API cursor attributes to something other than their defaults and then execute a DECLARE CURSOR and OPEN statement, you are asking SQL Server to map an API cursor over a Transact-SQL cursor. For example, do not set the ODBC attributes that call for mapping a keyset-driven cursor over a result set, and then use that statement handle to execute a DECLARE CURSOR and OPEN calling for an INSENSITIVE cursor.

A potential drawback of server cursors is that they currently do not support all Transact-SQL statements. Server cursors do not support Transact-SQL statements that generate multiple result sets; therefore, they cannot be used when the application executes a stored procedure or a batch that contain more than one SELECT statement. Server cursors also do not support SQL statements containing the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.

Server Cursors vs. Default Result Sets

Using a cursor is less efficient than using a default result set. In a default result set the only packet sent from the client to the server is the packet containing the statement to execute. When using a server cursor, each FETCH statement must be sent from the client to the server, where it must be parsed and compiled into an execution plan.

If a Transact-SQL statement will return a relatively small result set that can be cached in the memory available to the client application, and you know before executing the statement that you must retrieve the entire result set, use a default result set. Use server cursors only when cursor operations are required to support the functionality of the application, or when only part of the result set is likely to be retrieved.

Server Cursors vs. Client Cursors

There are several advantages to using server cursors instead of client cursors:

  • Performance

    If you are going to access a fraction of the data in the cursor (typical of many browsing applications), using server cursors provides optimal performance because only fetched data is sent over the network. Client cursors cache the entire result set on the client.

  • Additional cursor types

    If the SQL Server Native Client ODBC driver used only client cursors, it could support only forward-only and static cursors. By using API server cursors the driver can also support keyset-driven and dynamic cursors. SQL Server also supports the full range of cursor concurrency attributes only through server cursors. Client cursors are limited in the functionality they support.

  • More accurate positioned updates

    Server cursors directly support positioned operations, such as the ODBC SQLSetPos function or UPDATE and DELETE statements with the WHERE CURRENT OF clause. Client cursors, on the other hand, simulate positioned cursor updates by generating a Transact-SQL searched UPDATE statement, which leads to unintended updates if more than one row matches the WHERE clause conditions of the UPDATE statement.

  • Memory usage

    When using server cursors, the client does not need to cache large amounts of data or maintain information about the cursor position because the server does that.

  • Multiple active statements

    When using server cursors, no results are left outstanding on the connection between cursor operations. This allows you to have multiple cursor-based statements active at the same time.

The operation of all server cursors, except static or insensitive cursors, depends on the schema of the underlying tables. Any schema changes to those tables after a cursor has been declared results in an error on any subsequent operation on that cursor.