Choosing a Cursor Type

Choosing a cursor type depends on several variables, including:

• Size of the result set.
• Percentage of the data likely to be needed.
• Performance of the cursor open.
• Need for cursor operations, such as scrolling or positioned updates.
• Level of visibility to data modifications made by other users.

The default settings are fine for a small result set if no updating is done, but a dynamic cursor is preferred for a large result set in which the user is likely to find an answer before retrieving many of the rows.

Rules for Choosing a Cursor Type

Some simple rules to follow in choosing a cursor type are:

• Use default settings for singleton selects (returns one row), or other small result sets. It is more efficient to cache a small result set on the client and scroll through the cache instead of asking the server to implement a cursor.
• Use the default settings when fetching an entire result set to the client, such as when producing a report. Default result sets are the fastest way to transmit data to the client.
• Default result sets cannot be used if the application is using positioned updates.
• Default result sets cannot be used if the application is using multiple active statements. If cursors are being used only to support multiple active statements, choose fast forward-only cursors.
• Default result sets must be used for any Transact-SQL statement or batch of Transact-SQL statements that will generate multiple result sets.
• Dynamic cursors open faster than static or keyset-driven cursors. Internal temporary work tables must be built when static and keyset-driven cursors are opened, but they are not required for dynamic cursors.
• In joins, keyset-driven and static cursors can be faster than dynamic cursors.
• Keyset-driven or static cursors must be used if you want to do absolute fetches.
• Static and keyset-driven cursors increase the usage of tempdb. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb.
• If a cursor must remain open through a rollback operation, use a synchronous static cursor and set CURSOR_CLOSE_ON_COMMIT to OFF.

Each call to an API fetch function or method causes a roundtrip to the server when using server cursors. Applications should minimize these roundtrips by using block cursors with a reasonably large number of rows returned on each fetch.