Appropriate Use of Cursors

When should you use cursors? A somewhat simplistic answer is, "Only when you have to." This is not a totally facetious answer, because if you can think of a set-based solution to a problem and avoid using a cursor, you should do that. Ordinarily, cursors should be near the bottom of your list of possible solutions. Remember that SQL Server is a relational database, so it is inherently set based. As I mentioned earlier, if you overuse cursors, you turn SQL Server into an ISAM-like product and severely cripple its power.

You should not interpret this warning as a blanket statement that you should never use cursors. Scrollable cursors were, in fact, near the top of the new features wish list of many members of the SQL Server development team. More important, cursors were near the top of the developer community's wish list. Cursors are extremely powerful and provide SQL Server with capabilities that cannot be achieved with other products. So let's look at some situations in which cursors provide a great benefit.

Row-by-Row Operations

Transact-SQL cursors are great for row-by-row operations with a stored procedure or a single batch. For example, suppose a table has some financial data, and for each row of that data we want to perform one of several sets of conversions on the data. Each set of conversions is written as a separate stored procedure, so we want to send the data values from each row as parameters to the appropriate stored procedure. So, for example, if the value in the balance column is less than 1000, we want to call a procedure called BasicConversions; if the balance value is between 1000 and 10,000, we want to call a procedure called ExtendedConversions; and if the balance value is greater than 10,000, we want to call AdvancedConversions. You might think you could process this operation with CASE, but remember that in Transact-SQL CASE is an expression that returns a value; CASE doesn't allow you to choose completely different actions to perform. With Transact-SQL cursors, we can step through each row of the table, and depending on the value in the balance column for each row, we can conditionally determine which of the three stored procedures to execute.

Transact-SQL's FETCH command is almost always done in a loop, and the cursor is used to perform row-by-row processing on the entire result set. But you should not use Transact-SQL cursors across multiple batches. For example, you shouldn't issue singular FETCH commands as their own batches. If your application fetches rows, does some work, fetches more rows, and so on, you should use API server cursors, not Transact-SQL cursors.

Query Operations

Transact-SQL cursors are often used in conjunction with EXECUTE('string') to write and execute SQL statements based on the results of some query. For example, suppose we want to GRANT permission to UPDATE data in every table in a database to a particular group of users. SQL Server provides a built-in role in every database called db_datawriter, but this allows all three data modification operations: INSERT, UPDATE, and DELETE. To allow only UPDATE permission, we want to create a user-defined role called db_dataupdaters and GRANT UPDATE to this role on all user tables. Using Transact-SQL cursors, we can use the following generic batch to do the entire operation—without having to return the names of the tables to the calling client application, reformat them into the correct commands, and send them back to the server. Instead, the entire operation is done at the server; only the initial request is sent from the client application.

 EXEC sp_addrole 'db_dataupdaters' GO DECLARE tables_curs CURSOR FOR     SELECT name FROM sysobjects     WHERE type='U' -- user table, not system table OPEN tables_curs DECLARE @tablename varchar(30), @output_msg varchar(80) FETCH NEXT FROM tables_curs INTO @tablename WHILE (@@FETCH_STATUS=0 )     BEGIN     EXEC ('GRANT UPDATE ON ' + @tablename            + ' TO db_dataupdaters')         IF (@@ERROR=0)             SELECT @output_msg=                 'UPDATE permission granted on table '                 + @tablename         ELSE             SELECT @output_msg=                 'Failed to grant UPDATE permission on table '                 + @tablename + ' @@ERROR=' +                 CONVERT(varchar, @@ERROR)     PRINT @output_msg     FETCH NEXT FROM tables_curs INTO @tablename     END CLOSE tables_curs DEALLOCATE tables_curs 

Scrolling Applications

The need for Transact-SQL cursors was not the biggest motivation for adding cursors to SQL Server. Rather, the crying need was for API server cursors to support "scrolling applications." Many of these applications originated from ISAM applications or started out as single-user Microsoft FoxPro or dBASE applications (or they followed that paradigm—you know the type).

For example, think of an address book of the type used in your e-mail program. The user opens the address book and scrolls up or down to view the list of names. The user drags the scroll bar slider to the bottom and expects to be positioned at the last record. Dragging the slider to the beginning of the list, the user expects to be repositioned at the first record. Is this an appropriate use for a cursor? It depends on what you want to do with the information in the list. Here are some examples of how you might think about the problem.

EXAMPLE 1

A cursor is unnecessary if the address book includes only a few hundred entries, if it will be read-only, or if you're not concerned with sensitivity to changes in data (that is, you don't care whether the most up-to-date copy of the data is available). After the user's list box is initially populated, a few new names might be added or the information might change in some way. But for our purposes, it is unnecessary for the list box to exhibit dynamic behavior such as names appearing, disappearing, or changing as the user scrolls through the list. In this case and with these requirements, you can simply issue the query and get the results. You can easily buffer a few hundred rows of information on the client, so there is no need for cursors.

EXAMPLE 2

An API server cursor is appropriate if the address book has 100,000 entries. The user will probably look at only a few dozen entries and then quit. Should you select all 100,000 rows to populate the list box so that the user can see only a few dozen? In most cases, the answer is no. Using a cursor is reasonable in this situation; you can use an API server cursor.

Suppose the list box can display 20 names at a time. You can set the cursor width to 20 (a fat cursor) so that with any fetch forward or backward you get one list box worth of data. Or you can fetch 60 or 80 rows at a time so that the user can scroll by a few screenfuls within the application without having to ask the server for more rows. If the user moves the scroll bar slider to the bottom of the list, you fetch the bottom 20 rows by using LAST. If the user moves the slider to the top, you fetch the top 20 rows by using FIRST. And if the slider moves three-fourths of the way down the list, you can do some quick division and scroll to that point by using ABSOLUTE n. (For example, you can easily determine the total number of qualifying rows. If 100,000 rows are available and you want to scroll approximately three-fourths of the way down, you can do a FETCH ABSOLUTE 75000 statement.)

If you have to provide this type of application with a large amount of data, using a cursor makes sense. Of course, it might be better if the user is required to type in a few characters—that would allow the application to qualify the result set of the cursor. If you know that the user is interested only in names starting with S, for example, you can significantly reduce the size of the cursor by adding the appropriate criteria (for example, name LIKE ('S%') in the cursor SELECT statement.

Choosing a Cursor

Although it might seem as if you have an overabundance of choices for your cursor model, the decision-making process is fairly straightforward. Follow these guidelines to choose the appropriate cursor for your situation:

• If you can do the operation with a good set-oriented solution, do so and avoid using a cursor. (In ODBC parlance, use a firehose cursor.)
• If you've decided that a cursor is appropriate and you'll be doing multiple fetches over the network (such as to support a scrolling application), use an API server cursor. You'll use OLE DB, ODBC, RDO, DB-Library, ADO, or another API, depending on your application needs.
• For the most part, avoid using client-side cursors and products or libraries that perform many cursor operations in the client application. Such applications tend to make excessive requests to the server and use the server inefficiently—the multitude of network round-trips makes for a slow, sluggish application.

Table 13-1 compares the ways cursors are declared and opened and how operations are performed in Transact-SQL cursor statements; their rough equivalents among the pseudo-stored procedures used by the ODBC driver and DB-Library; and the ODBC and OLE DB cursor methods.

Table 13-1. Equivalent Transact-SQL cursor statements, pseudo-stored procedures, ODBC cursor functions, and OLE DB cursor methods.

Transact-SQL Cursor Statement	Pseudo-Stored Procedure	ODBC Cursor Function	OLE DB Cursors
DECLARE/OPEN	sp_cursoropen	SQLSetStmtAttr (SQL_ATTR_CURSOR__TYPE) SQLSetStmtAttr (SQL_ATTR_CONCURRENCY) SQLExecDirect or SQLExecute	Set row set properties OPEN such as DBPROP_OTHERINSERT, DBPROP_ OTHERUPDATEDELETE, DBPROP_OWNINSERT, or DBPROP_OWNUPDATEDELETE to control cursor behaviors
FETCH	sp_cursorfetch	SQLFetch or SQLExtendedFetch	IRowset::GetNextRows
UPDATE/DELETE (positioned)	sp_cursor	SQLSetPos	IRowsetChange::SetData or IRowsetChange::DeleteRows
CLOSE/DEALLOCATE	sp_cursorclose	SQLCloseCursor	IRowset::Release

Cursor Membership, Scrolling, and Sensitivity to Change

In addition to understanding the cursor model (that is, Transact-SQL or API server cursors), you must understand some other key options that deal with the "membership" of the cursor and the behavior the cursor exhibits as you scroll through the result set. We can divide these options into two categories:

• Cursor types The API server cursors usually specify cursor behavior by dividing them into four types: static, keyset, dynamic, and forward-only.
• Cursor behaviors The ANSI SQL-92 standard defines the keywords SCROLL and INSENSITIVE to specify the behavior of cursors. Some of the APIs also support defining a cursor's behavior using these terms. SQL Server 2000 allows you to specify many more aspects of the cursor's behavior.

Transact-SQL cursors can use either the ANSI-specified syntax with INSENSITIVE and SCROLL options or the full range of cursor types, which we call Transact-SQL Extended Syntax. Prior to SQL Server version 7, only the ANSI syntax was available. However, because Transact-SQL Extended Syntax, which allows all the various options available through API cursors, provides much greater control and flexibility, I'll use that syntax in most of my examples. The ANSI specification does not define standards for several semantic issues related to sensitivity to changes made by others. The issue of sensitivity to changes is further complicated in the client/server environment because using a FETCH statement means fetching across the network. When you use the ANSI specification to declare a cursor, the behavior characteristics are specified before the word CURSOR:

 DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_list]}] 

When implementing cursors, Microsoft engineers had to make a lot of decisions about subtle behavioral issues. They wanted efficient scrollable cursors that could be used for updating in a networked client/server environment. However, according to the ANSI specification, scrollable cursors are read-only! Fortunately, they were able to go way beyond the specification when they implemented scrollable, updateable cursors. In fact, Microsoft's original cursor specification went so far beyond the original ANSI specification that they started using the terminology of the four cursor types (static, keyset, dynamic, and forward-only) even though the syntax allowed only the words INSENSITIVE and SCROLL. Since the new API-based syntax for declaring cursors actually uses these types in the declaration, that is the syntax I'll mainly use.

Here's how to declare a cursor using 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_list]] 

The keywords describe how membership in the cursor is maintained (that is, which rows qualify), the available scrolling capabilities, and the cursor's sensitivity to change. Notice that the keywords appear after the word CURSOR in the DECLARE statement. That is how the parser distinguishes the older ANSI cursor syntax from the new Transact-SQL Extended Syntax for cursors: in the former, the cursor qualifiers come before the word CURSOR; in the latter, the qualifiers come after.

Static Cursors

A static cursor is attached to a snapshot of the data that qualifies for the cursor. The snapshot is stored in tempdb. A static cursor is read-only, and the rows in the cursor and the data values of the rows never change when you fetch anywhere in the cursor because you operate on a private, temporary copy of the data. Membership in a static cursor is fixed—that is, as you fetch in any direction, new rows cannot qualify and old rows cannot change in such a way that they no longer qualify.

Before you use a static cursor, consider whether you need to use a cursor at all. If the data is static and read-only, it probably makes more sense to process it all in the client application as a default result set and not use a cursor. If the number of rows is too large to reasonably process on the client, creating this temporary table on the server can also prove to be an expensive undertaking.

If you use ANSI-style cursors and specify the modifier INSENSITIVE, the cursor will be static. Using Transact-SQL Extended Syntax, you can actually specify the keyword STATIC. A private temporary table is created for the SELECT statement you specify. Note that the cursor cannot do positioned updates or deletes on this snapshot of data in the temporary table. For comparison, here are two cursor definitions for the same query, using the two syntax possibilities:

 -- ANSI-style syntax for declaring static cursor DECLARE my_cursor1 INSENSITIVE CURSOR  FOR SELECT au_id, au_lname FROM authors WHERE state != 'CA' -- Transact-SQL Extended Syntax for declaring static cursor DECLARE my_cursor1 CURSOR STATIC  FOR SELECT au_id, au_lname  FROM authors WHERE state != 'CA' 

The result rows returned from the two cursors above are identical, with one minor behavioral difference. A static cursor defined using the ANSI syntax and the keyword INSENSITIVE is not scrollable—that is, you can fetch rows only in the forward direction. If you want the cursor to be scrollable, you must specify the SCROLL keyword. The Transact-SQL Extended Syntax creates static cursors that are scrollable by default.

You might want to use a static cursor for some "what if"–type operations on the data when you know that the changes will never be reflected in the base tables. If you want to carry out this type of operation, you can do a SELECT INTO in a temporary table and then declare a cursor on that table without specifying STATIC. If you specify a query that performs an aggregate function (such as SUM, MIN, MAX, GROUP BY, or UNION), the cursor might also have to be materialized in a temporary table and therefore will automatically be a static cursor.

Keyset Cursors

With a keyset cursor, a list of all the key values for the rows that meet the SELECT statement criteria is kept in tempdb. For example, if you declare a scrollable cursor on the customer table, the keyset is a list of those cust_id values that qualified for membership in the SELECT statement when the cursor was opened. Suppose that when the cursor was opened, the SELECT statement used to declare the cursor had a WHERE clause in this form:

 WHERE cust_balance > 100000 

Suppose also that customers 7, 12, 18, 24, and 56 (that is, rows with cust_id of those values) qualified. These keys are used whenever fetching is performed. Conceptually, further selection for the cursor takes this form

 WHERE cust_id IN (7, 12, 18, 24, 56) 

rather than this form:

 WHERE cust_balance > 100000 

(Internally, the SELECT statement is not issued again. This is for purposes of illustration only.) Membership in the keyset cursor is fixed. That is, these five identified rows are part of the cursor and no other rows are seen in subsequent fetching. Even if other rows that meet the SELECT statement criteria are subsequently inserted or updated, they are not seen by this cursor after it is opened.

In a keyset cursor as opposed to a static cursor, you can see changes to the data in the rows that meet the SELECT criteria when the cursor is opened. For example, if another user modifies the customer balance while you hold the cursor open and then fetch the row again, you see those changes (unless you hold locks to prevent such changes). In fact, even if the modification causes the row to no longer satisfy the criteria of cust_balance > 100000, you still see the row in a subsequent fetch. The row disappears only if it is actually deleted. The key value still exists because it was squirreled away in tempdb, but the row is gone. Default values, NULL values, blank spaces, or zeros (as appropriate) are supplied for the column values. But more important, the @@FETCH_STATUS system function returns a value of -2 in this case, indicating that the row no longer exists.

Because the keys are stored in tempdb when the cursor is open and membership is fixed, keyset cursors can fetch to an absolute position within the result set. For example, it is reasonable to fetch to row 4 of the cursor:

 FETCH ABSOLUTE 4 FROM cursor_name 

It shouldn't come as a surprise that a keyset cursor demands that a unique index exist on every table used in the SELECT statement for the cursor. The unique index is necessary to identify the keys. The index can be created directly (with CREATE INDEX), or it can exist as a result of a Primary Key or Unique constraint.

NOTE

---

In SQL Server 2000, every clustered index is treated as unique internally, even if you didn't specifically define the index to be unique. For this reason, you can always define a keyset cursor on a table that has a clustered index.

If you declare a keyset cursor using Transact-SQL Extended Syntax on a table that doesn't have a unique index, you do not receive an error message or any warning unless the cursor was declared with the option TYPE_WARNING. Without that option specified, you just don't get a keyset cursor. Instead, the cursor is created as STATIC. Later in this chapter, we'll see the stored procedures that provide information about your currently available cursors.

Dynamic Cursors

You can think of a dynamic cursor as a cursor in which the SELECT statement is applied again in subsequent FETCH operations. That is, the cursor does not refetch specific rows, as in this statement:

 WHERE cust_id IN (12, 18, 24, 56, 7) 

Instead, conceptually, the WHERE clause is reapplied. For example:

 WHERE cust_balance > 100000 

This means that membership is not fixed—subsequent fetches might include newly qualifying rows, or previously qualifying rows might disappear. This can occur because of changes you have made within the cursor or because of changes made by others. If the cursor has not locked the rows of the result set (which is dependent on the concurrency options selected, as we'll see in a moment), the changes made by others are seen in your cursor when you do a subsequent fetch on the cursor. You see such changes only on a subsequent fetch. The image of data in the buffer from the last fetch is just that—a copy in memory of what the row or rows looked like when the last fetch was performed.

Since membership in a dynamic cursor is not fixed, there is no guarantee that subsequent fetches will always bring up the same information. For this reason, FETCH ABSOLUTE is not supported for cursors declared using the DYNAMIC keyword, even if you declare the cursor through the API. It doesn't make sense, for example, to simply fetch to row 104 if every subsequent fetch brings up a row 104 that contains different information. However, FETCH RELATIVE is supported with dynamic cursors, which sometimes surprises people. When used with a dynamic cursor, FETCH RELATIVE n starts fetching from the first row in the current cursor set and skips the first n rows from that point.

A cursor declared with an ORDER BY clause can be dynamic only if an index contains keys that match the ORDER BY clause. If no such index exists, the cursor automatically converts to either a keyset or a static cursor. As previously mentioned, you need a unique index on some column in the table to get a keyset cursor. So if you have a unique index that just doesn't happen to be on the columns in the ORDER BY, you get a keyset cursor. If there is no index that matches the ORDER BY and no unique index on any column, the cursor reverts to a static cursor.

Forward-Only Cursors

Forward-only cursors are dynamic cursors that allow only a FETCH type of NEXT. It's okay for you to think of SQL Server as having only three types of cursors (static, keyset, and dynamic). Forward-only is treated here as its own type because the Transact-SQL Extended Syntax, as well as API server cursors, specify forward-only at the same level as KEYSET and DYNAMIC.

Forward-only scrolling is consistent with the recommended use of Transact-SQL cursors to provide row-by-row processing within a stored procedure or batch. Such processing is usually from start to end—one way—so that rows are never refetched. If you use Transact-SQL cursors appropriately, you typically scroll forward-only to do a row-by-row operation and you do not revisit rows. Forward-only cursors are usually the fastest type of cursor, but a standard SELECT statement (not a cursor) is still significantly faster.

Fast Forward-Only Cursors

Transact-SQL Extended Syntax allows for a special type of forward-only cursor called a fast forward-only cursor. This type of cursor is always read-only, in addition to being forward-only. You can also specify fast forward-only cursors through the ODBC driver, and in fact this is the environment in which they provide the most benefit. Applications using the SQL Server ODBC driver can set the driver-specific statement attribute SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_FFO or SQL_CO_FFO_AF. The SQL_CO_FFO_AF option specifies that an autofetch option also be enabled. Autofetch enables two optimizations that can significantly reduce network traffic:

• When the cursor is opened, the first row or batch of rows is automatically fetched from the cursor. This saves you from having to send a separate fetch request across the network.
• When a fetch hits the end of the cursor, the cursor is automatically closed. This saves you from having to send a separate close request across the network.

The most dramatic improvement comes when you process cursors with relatively small result sets that can be cached in the memory of an application. The fast forward-only cursor with autofetch enabled is the most efficient way to get a result set into an ODBC application.

The autofetch option is not available when you use Transact-SQL Extended Syntax. It is meant as an optimization when you declare and manage cursors through your client application.

Implicit conversion of fast forward-only cursors Fast forward-only cursors are implicitly converted to other cursor types in certain situations. If the SELECT statement joins one or more tables with triggers to tables without triggers, the cursor is converted to a static cursor. If a fast forward-only cursor is not read-only, it is converted to a dynamic cursor. If the SELECT statement is a distributed query that references one or more remote tables on linked servers, the cursor is converted to a keyset-driven cursor.

Other implicit conversions are also carried out when you work with Transact-SQL Extended Syntax cursors, depending on the structure of the underlying tables and the keywords used in the cursor's SELECT statement. We saw previously that a cursor declared as a keyset is implicitly converted to a static cursor if any of the underlying tables don't have a unique index. A cursor based on a SELECT statement that needs to build a temp table is implicitly converted to a static cursor. If you have any doubt whether SQL Server actually created the type of cursor you requested, you can use the cursor procedures described in the following sections to verify all of the cursor's properties.