About 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.
If the data that is to be retrieved will be consumed all at once, and there is no need for positioned updates or scrolling, default result sets are recommended. SQL Server removes the restriction that prevented having more than one outstanding default result set, when using Multiple Active Result Sets (MARS).
Rules for Choosing a Cursor Type
Some simple rules to follow in choosing a cursor type are:
Use default result sets when possible. If scrolling is needed, it may still be 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 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.