Choosing a Cursor Type

Accessing and Changing Relational Data

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.

See Also

Cursor Types