Microsoft® SQL Server™ supports three cursor implementations:
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 that contains the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.
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.
There are several advantages to using server cursors instead of client cursors:
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.
If the SQL Server 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.
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.
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.
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.