Accessing and Changing Relational Data
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.