SQL Server allows you to process data within your application by using both set-based operations (called default result sets) and row-at-a-time techniques with server cursors (processed similarly to Btrieve operations). Each of these processing models has its own distinct role in the application development process. In the following section, these processing options are compared and contrasted to help you choose the one that satisfies the data processing requirements within your application.
When an application uses Transact-SQL effectively to request only the rows it needs from the server, the most efficient way to retrieve these rows is a default result set. An application requests a default result set by leaving the ODBC statement options at their default values prior to executing SQLExecute or SQLExecDirect. For a default result set, the server returns the rows from a Transact-SQL statement as quickly as possible and maintains no position within this set of rows. The client application is responsible for immediately fetching these rows into application memory. Most data processing activity within an application can and should be accomplished using default result sets. Default result sets offer several distinct advantages:
One or multiple default result sets can be returned with only one round-trip between the client and the server.
Between client calls, default result sets do not maintain position or other client state information at the server. This allows your application to support many users.
Stored procedures that return default result sets can also contain procedural logic, all at the server. This is more efficient than moving the data out of the server to apply procedural logic at the client. Stored procedures also allow data manipulation logic to be encapsulated and shared by all client applications.
Most data retrievals and modifications can be performed using SQL Server default result sets. However, in some cases, an application must use row-at-a-time capabilities to satisfy a certain application request. For example, the user may have to examine information one row at a time to make a decision or to use the information from the row to proceed with another task. SQL Server provides this functionality with server cursors. Like the Btrieve navigational model, server cursors maintain a position at the server. Server cursors act like an ISAM interface, except you cannot perform seek or find operations within the cursor result set. Cursors require that you perform this functionality within the client application.
Server cursors can be opened on the full table or any subset of a table just like default result sets. However, cursors differ from default result sets in the following distinct ways:
Depending on the type of cursor chosen (static, keyset-driven, forward-only, dynamic, and so on), the application can perform a variety of different fetching operations (FETCH FIRST, NEXT, PREVIOUS, ABSOLUTE, RELATIVE, and so on) within the scope of the cursor result set at the server. With a default result set, an application can fetch only in a forward direction.
When an application uses default result sets, shared locks may remain in effect until the client application processes the entire result set or cancels the operation. This can be a problem if the application waits for user input before it finishes fetching. If the application does not immediately fetch all data requested, the shared locks may be held for a long time, preventing others from updating or deleting. Because server cursors do not hold locks by default, they are unaffected by this application behavior.
Default result sets, because of their set-based nature, do not provide this functionality. If a client application needs to update or delete a row retrieved from a default result set, it must use a SQL UPDATE or DELETE statement with a WHERE clause containing the row’s primary key or call a stored procedure that takes the primary key and new values as parameters.
By using ODBC, server cursors allow you to have multiple active statements on a single connection. An application that uses default result sets must fetch result rows entirely before a connection can be used for another statement execution.
Server cursors within SQL Server are powerful, flexible, and useful for ISAM-like, row-at-a-time processing and for positional updates. However, server cursors incur some cost and have some limitations:
Opening a server cursor is more expensive than using default result sets within SQL Server in terms of parse, compile, and execute time and tempdb resources. The additional cost of a server cursor varies by query depending on the number of tables involved (for example, single table or join) and type chosen (static, keyset-driven, forward-only, dynamic, and so on).
Server cursors cannot be opened on Transact-SQL batches or stored procedures that return multiple result sets or involve conditional logic.
Fetching from a server cursor requires one round-trip from client to server to open the cursor and retrieve the rowset number of rows specified within the application. In ODBC terms, this means one round-trip per call to SQLFetchScroll (or SQLExtendedFetch in ODBC 2.0).
The rowset size that is used to process a server cursor affects the processing throughput of your application. In ODBC, you can communicate the number of rows to fetch at a time by using the ROWSET SIZE statement option. The size you choose for the rowset depends on the operations performed by the application. Screen-based applications commonly follow one of two strategies: Setting the rowset size to the number of rows displayed on the screen or setting the rowset size to a larger number. If the user resizes the screen, the application changes the rowset size accordingly. Setting the rowset size low causes unnecessary fetching overhead. Setting the rowset size to a larger number, such as 100, reduces the number of fetches between the client and the server needed to scroll through the result set. Setting the rowset size high requires a larger buffer on the client, but minimizes the number of round-trips needed to return the result set between the client and server. An application that buffers many rows at the client enables the application to scroll within the client-side buffer instead of repeatedly requesting data from the server. In that case, the application only fetches additional rows when the user scrolls outside of the buffered portion of the result set.
You should carefully analyze the scope of each task within the application to decide whether default result sets or server cursors should be used. Default result sets should be used as much as possible. They require fewer resources, and their result sets are easier to process than those of server-side cursors. When a data request will retrieve a small number of rows or only one, and the application does not require positional updates, be sure to use a default result set.
Server cursors should be used sparingly and should be considered only for row-at-a-time processing within the application. If your application requires server cursors despite their performance disadvantages, make sure that the rowset size is set to a reasonably high value.