Working with Cursors | Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

Throughout this book I have told you that data is returned to clients as a result set-the set of rows resulting from a query. At the client the result set is looped through by a client process, which populates a grid or a collection of fields. Sharp queries allow us to return a single row of data that is used to populate the fields, but often we are not sure exactly which row is required and thus we send back as small a result set as possible and allow the user to make the final choice.

There are situations that you may find yourself in, in which it is impossible to work with a result set returned to the client. Online or Internet applications are a good example, in which a result set cannot be returned to the client for further processing. A browser has no functionality to accommodate a long stream of rows coming up the wire (although XML is beginning to make this a reality). Still, if a result set will disappear into a black hole when returned to the client, you need a facility to work with the result set on the server. In other words, instead of holding the result set at the client, the result set is stored on the server, where the client can work with it one row at a time. This is the essence of a cursor.

You could consider a cursor to be an extension to result set architecture because it enables the following capabilities:

You can retrieve one row or a block of rows from the result set on the server.
You can take aim at a specific row on the result set, a feature called row positioning.
You can modify data at the current position in the result set.

A cursor also allows you to set different levels of visibility to changes made by other users to the underlying data of the result set. And you can access the data from scripts, stored procedures, and triggers.

There are two methods for requesting a cursor on a result set:

You can use T-SQL, which is compliant with SQL-92 cursor syntax.
You can use an API or Object Model that supports using cursors. (APIs include DB-Library, OLE DB, and ODBC. And the ADO.NET Object Model supports cursor functionality.)

Caution

Never mix cursor request methods in your application. If you are going native (such as ODBC) stick to the native API If you use an ADO.NET object, then use the ADO.NET model only.

SQL Server cursors have to be explicitly called. If you do not use a cursor call, SQL Server defaults to returning the result set to the client.

Types of Cursors

SQL Server provides support for three types of cursors:

T-SQL Cursors These are created using the DECLARE CURSOR syntax and are used mainly in T-SQL scripts, stored procedures, and triggers. These cursors are instantiated on the server by T-SQL statements transmitted by the client. The DECLARE CURSOR syntax is also often used in stored procedures, T-SQL batches, and triggers. Using cursors in triggers is tricky, however, and badly written or very long and involved “cursorized” triggers can bring a SQL Server instance to dead stop.
API Cursors These cursors are also server-based and are instantiated by API calls from the likes of OLE DB, ODBC, and DB-Library.
Client Cursors These cursors are implemented on the client though either API or Object Model features. While the result set is still supported at the server, the result set rows are cached in a client facility. So the rows that are modified are the rows that are cached at the client.

You should know that many seasoned SQL Server developers eschew cursors unless they are really needed. The reason for this is simple: performance. The result set is maintained on the server, and so SQL Server has to commit resources to the cursor. When a result set is sent to the client, SQL Server is done with the query and moves on to something else.

Cursors also have limitations that might not help you. For example, they do not support all T-SQL statements, and you cannot use statements that generate multiple result sets-so that means you are limited to one SELECT statement in a cursor-creating batch. Statements that contain the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO are SQL non grata for cursors.

Why is a cursor less efficient than a default result set? The answer is this: When you query SQL Server for data, a default result set is created by a batch of code, in one connection, sent to the server from a client or from a stored procedure. But with cursors every time you need data, you have to send out a FETCH statement from the client to the server, and every FETCH is parsed and compiled into an execution plan.

Getting back to the data conservation philosophy expounded in this book, you should rather code tight queries that aim to return the exact data needed by the client. If the result set is small, or you use new modern technologies such as XML (see Chapter 16 and later in this chapter), you might be able to avoid a cursor and gain not only in the performance arena, but also in the feature and complexity arenas.

If you need to use a cursor, do this: Favor server cursors and add more resources to the server if you need to because client cursors cache the entire result set on the client and that’s not very efficient. Remember, think “thinner is better” for the client. On the Internet or a widely distributed application you never really know what resources the client has, and asking a client to cache a cursor is like firing a torpedo that has delayed detonation technology.

Server cursors also by their nature support more features. For example, a server cursor can directly support positioned operations such as update and delete statements. Client cursors make updates and deletes a lot more difficult to manage and are more complex to code. Server cursors also allow your application to open concurrent cursor connections and support multiple active statements to those cursors.

T-SQL Cursors

I could write an entire book on using cursors and demonstrate them in the various APIs and object models, but since this is a SQL Server reference, let’s stick to T-SQL cursors.

Where can you use a T-SQL cursor? As mentioned earlier, they are best used in standard SQL scripts, from stored procedures, and in triggers. I am not in favor of using them in triggers. Triggers should be used for enforcing business rules and not iterating through cursors. Sure, there will be times when you need to work with a collection of rows in a cursor, but most of the time you can do the job with the standard default result set held on the server, which gives you cursor benefits (server-held result sets) without the overhead. If your solution calls for a trigger-created cursor, try to find a way around it, and as a last resort, keep the trigger-cursor code small.

Note

A T-SQL cursor does not support the fetching of a block of rows, but I do not see this as a big drawback.

The following steps take you through generating a T-SQL cursor:

First, you need to declare T-SQL variables that will hold the data returned by the cursor. You will need to declare one variable for each result set column. The variables should be large enough to hold the values. You should also use data types that can be implicitly converted from the data type of the column.
Next, you need to associate the cursor with a SELECT statement. This is done using the DECLARE CURSOR statement. The statement also defines the characteristics of the cursor. It gives the cursor its name and properties, such as whether the cursor is read only or forward scrolling.
The OPEN statement is then used to run the SELECT statement into the cursor.
FETCH INTO then gets individual rows and puts the data into the variables you created. You can also use other statements to work with variables.
When you are done with the cursor, get rid of it using the CLOSE statement. By closing the cursor, you free up resources and drop locks and the result set. But the same cursor can still be resurrected by reissuing the OPEN statement. This also means that the name you gave the cursor is still in circulation and cannot be used in another DECLARE CURSOR statement.
Finally, you use the DEALLOCATE statement to completely destroy the cursor, its name, and its properties. The name becomes available again, and you cannot resurrect a deallocated cursor.

The T-SQL cursor syntax is as follows (SQL-92 and T-SQL extensions):

 ---SQL-92 Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY UPDATE [ OF column_name [ , …n] ] } ] ---Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [FOR UPDATE [OF column_name [ , …n ] ] ]

Consult SQL Server Books Online for the complete explanation of the arguments. The following code is an example of a simple DECLARE CURSOR statement in action:

 DECLARE CustomerCursor CURSOR   FOR SELECT * FROM CustDetails

Next, you open the cursor thus

 OPEN CustomerCursor

and then fetch the first record as follows:

 FETCH NEXT FROM CustomerCursor

Each time that you issue the FETCH NEXT statement, you advance one row in the cursor. In the preceding example the FETCH NEXT is the only FETCH you can use because the SCROLL option was not specified. In other words, the cursor rolls forward only (remember the DAO forward-scrolling snapshot recordset). The more complex the cursor and the more options you enable on it, the more resources required to operate the cursor and the less efficient it will be. The complete FETCH SYNTAX is as follows:

 FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar} | RELATIVE { n | @nvar} ] FROM ] { { [ GLOBAL ] cursor_name }  | @cursor_variable_name } [ INTO @variable_name [ , …n ] ]

Monitoring a T-SQL Cursor

As if I needed to tell you, SQL Server comes equipped with an assortment of system stored procedures and functions you can use to monitor and track cursors. (See Appendix for the cursor stored procedures and functions.) To get a list of cursors that are visible to your connection, you can execute the sp_cursor_list stored procedure. Then you can use the several described cursor stored procedures to determine the characteristics of the cursor. The @@FETCH_STATUS returns the status of the last fetch.

Each fetch positions the cursor on the row whose data was just returned to you. The cursor does not advance a row after the data is returned but rather fetches the data of the row in front of it and then moves forward. The fetched row is thus called the current row, and you can then execute an UPDATE or DELETE on the current row.

Of course what we have discussed on cursors is just enough to tickle your taste buds, and the subject warrants a few chapters of its own. See my book’s sister reference SQL Server 2005 Developer’s Guide, by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2005), for more client “how to” stuff.