Application Cursors
When a client application requests information from SQL Server using the default settings in ADO, OLE DB, ODBC, or DB-Library, SQL Server must follow this process:
The client application sends a request to SQL Server in a network package. This request can be any Transact-SQL statement or a batch containing multiple statements.
SQL Server interprets the request and creates a query plan to solve the request. The query plan is compiled and executed.
SQL Server packages the results in the minimum number of network packets and sends them to the user.
The clients start receiving network packets, and these packets are waiting in the network buffer for the application to request them.
The client application receives the information contained in the network packages row by row.
The client application cannot send any other statement through this connection until the complete result set is retrieved or cancelled.
This is the most efficient way to retrieve information from SQL Server, and it is called a default result set. It is equivalent to a FORWARD_ONLY READ_ONLY cursor with a row set size set to one row.
Note
Some articles and books refer to the default result set as a "Firehose" cursor, which is considered an obsolete term.
SQL Server supports three types of cursors:
Transact-SQL cursors—These are the cursors you studied in the previous sections of this chapter.
Application Programming Interface (API) server cursors—These are cursors created in SQL Server, following requests from the database library, such as ADO, OLE DB, ODBC, or DB-Library. Listings 12.1 and 12.3 contain examples of this type of cursor.
Client cursors—These cursors are implemented in the client side by the database library. The client cache contains the complete set of rows returned by the cursor, and it is unnecessary to have any communication to the server to navigate the cursor.
Caution
Do not mix API cursors with Transact-SQL cursors from a client application, or SQL Server will try to map an API cursor over Transact-SQL cursors, with unexpected results.
Tip
Use Transact-SQL cursors in stored procedures and triggers and as local cursors in Transact-SQL batches, to implement cursors that do not require user interaction.
Use API cursors from client applications where the cursor navigation requires user interaction.
Using a default result set is more efficient than using a server cursor, as commented in previous sections in this chapter.
Caution
You cannot open a server cursor in a stored procedure or batch if it contains anything other than a single SELECT statement with some specific Transact-SQL statements. In these cases, use a client cursor instead.
Using server cursors is more efficient than using client cursors because client cursors must cache the complete result set in the client side, whereas server cursors send to the client the fetched rows only. To open a client cursor using ADO, you can set the CursorLocation property to adUseClient in the Connection or Recordset objects. The default value is adUseServer for server API cursor.
What's Next?
In this chapter, you learned how to use Transact-SQL cursors.
In Chapter 13, you will learn about transactions and locks, which are both important aspects of using cursors.
The concurrency of a database application depends directly on how the application manages transactions and locks.