Comparing Default Result Sets and Cursors

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.

Default Result Sets

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:

SQL Server Cursors

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 Cursor Differences

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:

Server Cursor Performance Costs and Limitations

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:

Determining the Rowset Size of a SQL Server Cursor

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.

Conclusions: Default Result Sets
and SQL Server Cursors

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.