Using Server Cursors

There are several advantages to using server cursors, which are implemented in SQL Server 6.x.

• Performance — If you are going to access a small fraction of the data in the cursor (typical of many browsing applications), using server based cursors will give you an optimal performance because only required data (and not the entire results set) is sent over the network.
• Additional cursor types — Keyset and dynamic cursor types are only available if you use server cursors. In addition, the cursor library only supports forward only with read-only concurrency and static with read-only and optimistic concurrency. With server based cursors, you can use the full range of concurrency values with the different cursor types.
• Cleaner semantics — The cursor library simulates cursor updates by generating a SQL searched update statement. This can sometimes lead to unintended updates.
• Memory usage — When using server based cursors, the client does not need to cache large amounts of data or maintain information about the cursor position; the server provides that functionality.
• Multiple open cursors — When using server based cursors, the connection between the client and the server does not remain busy between cursor operations. This allows you to have multiple cursor hstmts active at the same time. The ODBC specification still restricts you to one cursor per hstmt. To get around the one active hstmt limitation associated with Type A statements, change the statement options so you get server cursors associated with Type B statements.