How Are Server-Side Cursors Managed?

All of my Hitchhiker's Guide books have discussed cursors at lengthat least the ones where ADO, RDO, or DAO implemented them. Since ADO.NET does not support this paradigm, I'll need to add a few paragraphs here to refresh your understanding of the mechanics and terminology.

First, when you execute a query to build a server-side cursor (or any cursor, for that matter), SQL Server looks through the database to find qualifying rows. Your WHERE clause and other SELECT statement operators determine which rows are to be included and which are not. While this operation is not instantaneous, the rows found become part of the cursor "membership." If there is no WHERE clause in the SELECT query, all rows in the target table(s) are made members of the cursorthat can be expensive. Remember that server-side cursors (like a keyset cursor) are simply a temporary collection of pointers to the actual rows.

There are several types of server-side cursors^[1]:

^[1] See "DECLARE CURSOR" (Transact SQL) in BOL.

• Static cursors contain a scrollable set of rowsnot just keys. SQL Server creates a one-time temporary copy of the data in TempDb, so membership is fixed. References to the cursor are fetched from this temporary table. Static cursors are not updateable.

• Keyset cursors contain a set of keys used to fetch the actual rows. SQL Server creates a one-time temporary table that contains PKs of member rows, so membership is fixed. References to cursor rows are fetched using keys from this temporary table. As changes are made to the database (to non-key columns), these values are made visible when the row is fetched (or refetched). If a member row is deleted, @@FetchStatus returns 2. Rows addressed by keyset cursors are updateable through use of the UPDATE WHERE CURRENT OF syntax (that's illustrated in the example application). You can position to any specific member row using the ABSOLUTE fetch option or scroll through rows using FETCH NEXT, PRIOR, LAST, and FIRST fetch options. Keyset cursors default to SCROLL (scrollable) positioning but can be set to FORWARD_ONLYhowever, that does not make much sense to me.

• Dynamic cursors are like keyset cursors in most respects, except that membership is dynamicthat's because each time a Dynamic cursor row is accessed, the SELECT query is re-executed to repopulate the membership. These cursors are expensive and do not support absolute positioning.

• Forward-Only is an option for the other cursor types that disables the ability to scroll through the rows. FETCH NEXT is the only available fetch option for FORWARD_ONLY cursors. When declared without the STATIC, KEYSET, or DYNAMIC keywords, the FORWARD_ONLY cursor defaults to DYNAMIC (which means that you should always indicate one of the other types).

Other cursor options permit you to specify if the cursor can be "seen" by other applications accessing SQL Serverbeyond your application's connection. See SQL Server 2005 BOL for more specific details on these options. They describe how to make cursors visible to stored procedures and elsewhere.

What Do Server-Side Cursors Cost?

One of the downsides of server-side cursors is the burden they impose on T-SQL Server and the scalability of your applications. When you create a server-side cursor, T-SQL Server writes a set of keys or entire rows to memory or TempDB. This process is repeated and its effects multiplied for each application that opens a server-side cursor. Using server-side cursors also assumes that the connection remains open as long as the cursor's data is needed. This is definitely not a good idea for a Web-based (ASP/ASPX) application because the connection is usually closed, and its resources released, just after its queries are executed. On the other hand, for many traditional client/server applications, server-side cursors have been an effective way to manage data and simplify application designwhen used wisely.