Cursors

Cursors

In contrast to most programming languages, SQL is a set-based processing language. You retrieve sets of rows, update sets of rows, and delete sets of rows. The set of rows affected is determined by the search conditions specified in the query. Unfortunately, most programmers are used to doing record-oriented operations on data and often want to apply the same technique to SQL Server data. Admittedly, at times, processing rows as a single resultset with a single query can seem difficult or impossible .

For example, perhaps you want to update the prices of books in the titles table. For books priced less than $15, you want to increase the price by 15 percent. For books priced $15 or more, you want to decrease the price by 10 percent. For books with ytd_sales less than 500, you want to decrease the price by 25 percent.

To perform this update using set-oriented processing, you need to use three separate updates:

 update titles     set price = price * 1.15    where price <  update titles    set price = price * .9    where price >=  update titles    set price = price * .25    where ytd_sales < 500 

Immediately, you can see a potential problem here. If you have a book priced at $14.95, the first update will increase the price by 15 percent. Because the new price will now be $17.19, the second update will subsequently decrease the price by 10 percent. This is not the intended result.

Assuming you don't know about the CASE expression, how do you solve this problem using set-oriented processing?

NOTE

Cursors are one of the common causes of performance problems in SQL Server applications and should almost always be avoided unless no better solution is available. Later in this section, ways to avoid using cursors, as well as instances when cursors might be the best solution, will be discussed.

One approach would be to select the different rows into three temporary tables and update the rows in the titles table by joining between the temporary tables and the titles table. This solution is not ideal because you would have to execute three selects against the titles table to get the data into the temp tables. You would incur the overhead to insert the data into the temporary tables, and then another three lookups against the titles table to update the prices. You could minimize the overhead of the temporary tables by using table variables , but you would still have to make six passes of the titles table, which would be inefficient.

Another approach would be to add an update_flag bit column to the titles table. As you process each update, you would set the update_flag for the rows you update to 1. The next update would update the qualifying rows matching the search criteria where the update flag is still 0. This prevents rows from being modified more than once. However, you would still have to make three separate passes against the table, and then a fourth to clear the update flags.

However, it might be easier to write a query using cursors, examining each row individually to see whether it fits any of the criteria, and applying the appropriate update. With a cursor, you can make a single pass of the table, looping through each row, saving the relevant column information in local variables, and performing the appropriate calculations and updates as you go.

NOTE

You might be thinking at this point that you could just use the CASE expression instead of a cursor and still perform the update in a single pass of the table. You would be right; however, this chapter will continue to look at a cursor solution. Later in this chapter you'll learn why you might or might not want to use a CASE expression instead.

Cursor Example and Some Syntax

In Listing 26.27, you find the code for performing the update using a cursor. Take a look at the complete code for now; the different components will be explained in detail later.

Listing 26.27 Updating the titles Table Using a Cursor

 /* This is a SQL script to update book prices dependent on current price and ytd_sales */ /*declare cursor*/ declare titles_curs cursor for   select ytd_sales, price from titles   for update of price declare @ytd_sales int, @price money open titles_curs fetch next from titles_curs into @ytd_sales, @price if (@@fetch_status = -1) begin     print "No books found"     close titles_curs     deallocate titles_curs     return end while (@@fetch_status = 0) begin     if @ytd_sales < 500        update titles set price = @price * .75           where current of titles_curs     else        if @price >             update titles set price = @price * .9               where current of titles_curs        else            update titles set price = @price * 1.15               where current of titles_curs        fetch next from titles_curs into @ytd_sales, @price end if (@@fetch_status = -2)     raiserror ('Attempt to fetch a row failed', 16, 1) close titles_curs deallocate titles_curs 

Declaring Cursors

A cursor is declared for a SELECT statement. The ANSI defines the following syntax to declare a cursor:

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

SQL Server Version 7.0 introduced an alternative way of declaring a cursor to give it the same capabilities as an API-based cursor. API cursors are discussed later in this chapter.

NOTE

The different cursor types, API and T-SQL cursors, actually use the same code when executing in SQL Server, so it makes sense that you have the ability to use the same features with T-SQL level cursors that you have at the API level.

The syntax for Transact -SQL cursors is as follows :

 DECLARE  cursor_name  CURSOR       [LOCAL  GLOBAL]      [FORWARD_ONLY  SCROLL]      [STATIC  KEYSET  DYNAMIC]      [READ_ONLY  SCROLL_LOCKS  OPTIMISTIC]    FOR  select_statement  [FOR {READ ONLY  UPDATE [OF  column_list  ]}] 

You cannot use COMPUTE , COMPUTE BY , FOR BROWSE , or INTO in the select_statement .

Let's use your example to discuss the DECLARE statement:

 declare titles_curs cursor for     select ytd_sales, price from titles    for update of price 

The cursor declares a SELECT statement that reads from a single table. As for all queries, it is a good idea to limit the number of rows that the cursor will process through a WHERE clause. Don't use the cursor to filter out rows you don't want to process. Also, if SQL Server can use an index to find the rows, it's even better.

Because you are going to be updating the data based on the cursor position, you explicitly declare the cursor for UPDATE . Updateable cursors are the default, but it's best to explicitly specify the purpose of the query. If you are not going to be modifying rows in the cursor resultset, be sure to declare it as READ ONLY so SQL Server uses the appropriate locking strategy. Specifying the specific column(s) that will be updated helps SQL Server identify a unique index on the table to use that does not contain a column that will be modified within the cursor. If you do not specify a column_list , all columns are updateable.

Cursors that use the DISTINCT keyword, computed values, or aggregates can only be declared as READ ONLY because the cursor resultset is based on the contents of a work table and does not reflect actual data rows in a table.

The cursor name must follow the general rules for identifiers.

Local and Global Cursors

A new feature introduced in version 7.0 enables you to specify whether the cursor should be local or global.

Global cursors remain defined for a connection until they are explicitly deallocated, or until they are implicitly deallocated at termination of the connection.

A local cursor's scope is limited to the batch, stored procedure, or trigger in which it is defined. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates unless a reference to it is passed to a calling stored procedure, batch, and so on via a cursor variable. The cursor will then go out of scope when the last variable referring to it goes out of scope (for more information on cursor variables, see the section "Using CURSOR Variables" later in this chapter).

If neither GLOBAL nor LOCAL is specified when the cursor is declared, the default is determined by the setting of the database option default to local cursor . In SQL Server 2000, this option defaults to FALSE to match versions of SQL Server prior to 7.0, in which all cursors were global. It's possible that the default of this option will change in future versions of SQL Server, so it is a good idea to always specify explicitly whether the cursor should be local or global.

If you are writing a recursive stored procedure that uses cursors, you might need to declare the cursor as local to prevent SQL Server from generating an error message when the subprocedure tries to declare the cursor again with the same name. For an example of using a cursor in a recursive stored procedure, see the "Recursive Stored Procedures" section in Chapter 28.

STATIC and INSENSITIVE Cursors

STATIC and INSENSITIVE cursors are pretty much the same thing. STATIC is the T-SQL style syntax, and INSENSITIVE is the ANSI-style syntax. Both are populated with a snapshot of the data at the time the cursor is opened. The data is stored in a work table in tempdb. Any inserts, updates, or deletions to the table(s) from which the data originated do not affect the rows in the cursor. The rows in the cursor are not affected by any inserts , updates, or deletes to the table(s) the data came from. STATIC and INSENSITIVE cursors are read-only and cannot be updated. A static or insensitive cursor is useful if you do not want to be disturbed by changes to the underlying data while you process the cursor data.

The only real difference between STATIC and INSENSITIVE cursors is that fetches are Forward Only by default for INSENSITIVE cursors unless the SCROLL option is specified. STATIC cursors are scrollable by default.

KEYSET Cursors

For KEYSET cursors, a list of the key values for the rows meeting the SELECT statement criteria are put into a work table in tempdb instead of the entire resultset. The membership of a KEYSET cursor is still static; however, you will be able to see changes to any of the rows to which the KEYSET cursor points, even if the row is modified to no longer meet the original SELECT statement criteria. Only if a row is deleted will it disappear from the resultset. KEYSET cursors are scrollable. To build a keyset, KEYSET cursors require that a unique index exists on the underlying table(s).

NOTE

If a clustered index exists on a table, it is treated internally as a unique index even if it is not explicitly defined as such. Any table that has a clustered index on it can be accessed with a KEYSET cursor. If no unique or clustered index is available on a table, and KEYSET is specified, SQL Server will create the cursor as STATIC instead.

DYNAMIC Cursors

With DYNAMIC cursors, the membership is not fixed, and rows that didn't exist when the cursor was opened can qualify for a subsequent fetch. Likewise, any qualifying rows that have been deleted will disappear from the resultset. Because the rows in the cursor have not been locked, the cursor will see changes made to the data rows.

If a DYNAMIC cursor contains an ORDER BY clause, and no index exists to retrieve the data in the order specified by the ORDER BY clause, the cursor will be opened as a KEYSET or STATIC cursor, depending on whether a unique index exists on the table. This is because when an ORDER BY is specified and an index is not available to retrieve the data rows in sorted order, a work table is needed to sort the resultset. If a work table is needed to sort the resultset or to remove duplicate rows (as for an ORDER BY , UNION , or DISTINCT ), the cursor is operating from a work table and not the underlying base table.

FORWARD_ONLY Cursors

If no specific cursor type options are specified, FORWARD ONLY is the default. A FORWARD ONLY cursor is a dynamic cursor that can only fetch the next row in forward order. Rows cannot be revisited. FORWARD ONLY cursors are typically the fastest type of cursor, but they are still slower than a normal SELECT statement.

Opening Cursors

When you open a cursor, the SELECT statement is initiated to begin populating the cursor. For cursors declared with the INSENSITIVE or STATIC option, OPEN creates and populates a work table in tempdb with the cursor resultset. For KEYSET cursors, a work table in tempdb is populated with the unique keys for each row in the cursor resultset. When you specify the OPEN command, the cursor will be positioned above the first row:

 OPEN lead_cur 

You can check how many rows the resultset contains with the function @@CURSOR_ROWS . If the value is “1, the cursor is being populated asynchronously, as for a dynamic cursor or forward-only cursor.

If you close a cursor and open it again, the SELECT statement is re-executed and the cursor starts over at the beginning of the resultset. For FORWARD-ONLY cursors, this is the only way to return to a previous row in the cursor.

Declaring Variables

How do you process the values returned from the cursor?

You could simply display each row to the user as a one-row resultset, but that does not make much sense. It would be much simpler to issue an ordinary SELECT statement and display the rows to the user as you need to read them from the input buffer.

What you really want to do is store the values for some of the columns into local variables and perform some processing based on those values.

Note that you have chosen the same variable names as those returned from the SELECT statement. This makes it easier to remember the variable names when processing the cursor, and it makes it easier to maintain the code:

 declare @ytd_sales int, @price money 

Fetching Rows

After the cursor is opened, it is time to start reading rows from your cursor, which you do with the FETCH command:

 fetch from titles_curs into @ytd_sales, @price 

The default for FETCH is to get the next row from the cursor. Scrolling capabilities for FETCH are discussed in the next section. If the number of variables specified in the FETCH statement does not match the number of columns in the cursor SELECT statement, you will get a runtime error. You will also get a runtime error if the variable datatype does not match the column datatype and SQL Server cannot perform an implicit datatype conversion.

Scrollable Cursors

If you declare the cursor with the SCROLL keyword, you can navigate as you want within the resultset. For a scrollable cursor, you use the FETCH statement to navigate forward, backward, or to an absolute row in the resultset. For example, following is a scrollable version of a cursor:

 declare leads_curs scroll cursor for     select cust_id, est_sale, close_date, prob, sales_id, descr       from leads    for read only open leads_curs 

The following is the complete syntax for the FETCH command:

 FETCH [[NEXTPRIORFIRSTLASTABSOLUTE  n  RELATIVE  n  ] FROM  cursor_name  [INTO  @variable_name1, @variable_name2,  ...] 

The default if you don't specify a navigational option is to fetch the next row. The keyword NEXT is optional.

To fetch the previous row in the resultset, execute the following:

 fetch prior from leads_curs       into @cust_id, @est_sale, @close_date, @prob, @sales_id, @descr 

In this case, the cursor moves back one row. If the cursor is already on the first row of the set, the value of @@fetch_status is set to “1 to indicate that the fetch exceeded the cursor set.

This example retrieves the first row in the set:

 fetch first from leads_curs       into @cust_id, @est_sale, @close_date, @prob, @sales_id, @descr 

Use last to fetch the last row in the set.

You also can retrieve a row in an absolute position in the set. For example, to retrieve the tenth row in the cursor, use this:

 fetch absolute 10 from leads_curs       into @cust_id, @est_sale, @close_date, @prob, @sales_id, @descr 

Again, your program should check the value of @@fetch_status to be certain that the absolute position is valid. The value to the ABSOLUTE option can be a variable so your SQL code can dynamically specify the absolute row to fetch.

Scrollable cursors are most useful as API cursors, where a user, for instance, can move up and down a list box and choose some entry based on a cursor value.

The last fetch operator uses relative row scrolling:

 fetch relative -5 from leads_curs       into @cust_id, @est_sale, @close_date, @prob, @sales_id, @descr 

Relative scrolling can be forward (positive values) or backward (negative values). This example scrolls back five rows in the set. Again, always check @@fetch_status after each relative scroll to make sure the new row position is still valid.

The Main Loop

The main loop is where the real processing occurs. The loop looks like this:

 while (@@fetch_status = 0)  begin     if @ytd_sales < 500     begin         update titles set price = @price * .75            where current of titles_curs     end     else     begin         if @price >          begin             update titles set price = @price * .9                where current of titles_curs         end         else         begin             update titles set price = @price * 1.15                where current of titles_curs         end     end     fetch next from titles_curs into @ytd_sales, @price end 

You loop while @@FETCH_STATUS = 0 . A value of “1 means that you have fetched beyond the last row or first row of the cursor. A value of “2 means that the row that you are trying to fetch has been deleted. When the @@FETCH_STATUS is a value other than 0, all columns returned will be NULL .

Modifying Cursor Rows

To update or delete the currently fetched row in a cursor resultset, SQL Server provides the WHERE CURRENT OF cursor_name clause, which you can use in UPDATE and DELETE statements:

 UPDATE  table_name  SET  column  =  expression  [,  column  =  expression  [, ...]]    WHERE CURRENT OF  cursor_name  DELETE  table_name  WHERE CURRENT OF  cursor_name  

To update or delete rows using the WHERE CURRENT OF clause, you need to declare the cursor as updateable.

If the cursor definition includes a join and you plan to update only one table in the cursor, but need to access information in another, the cursor should be declared FOR UPDATE . You can use the SHARED keyword to indicate which tables you will only be reading from and which will be modified. In the following example, you need to access the publishers table to increase prices on titles published in Massachusetts, but no modifications will be made to the publishers table:

 declare tp cursor for     select title_id, type, price       from titles t, publishers p shared       where t.pub_id = p.pub_id         and state = "MA"    for update of price 

In addition to limiting updates to the price column of the titles table, this cursor also maintains only shared locks on the publishers table, improving multiuser operations.

Closing the Cursor

Close the cursor as soon as you don't need it anymore. Open cursors hold locks on the underlying tables and use valuable resources.

 close titles_curs 

You can reopen the cursor after it has been closed. The SELECT statement is executed again and the cursor is repopulated.

Deallocating Cursors

When you are finished with the cursor definition, you deallocate it. You cannot declare a GLOBAL cursor, or LOCAL cursor within the same scope with the same name until you have deallocated the previous cursor:

 DEALLOCATE titles_curs 

It is a good idea to deallocate a cursor when you no longer need it. The query plan is released from memory at that time, and it makes the structure of your code clearer.

Using CURSOR Variables

SQL Server Version 7.0 introduced the cursor datatype that you can use to define cursor variables. To assign a cursor to a cursor variable, you have to use the SET statement. You cannot set a cursor variable with an assignment select statement. You can use cursor variables in OPEN , FETCH , CLOSE , and DEALLOCATE commands as well as input or output parameters for stored procedures.

The following example demonstrates declaring a cursor and assigning it to a cursor variable:

 declare leads_curs scroll cursor for     select cust_id, est_sale, close_date, prob, sales_id, descr       from leads    for read only declare @curs_var CURSOR SET @curs_var = leads_crus 

You can also declare and set a cursor variable in a SET statement alone:

 declare @curs_var CURSOR  SET @curs_var = cursor scroll for    select cust_id, est_sale, close_date, prob, sales_id, descr       from leads    for read only 

Listing 26.28 demonstrates a general stored procedure that returns the names of all tables in a database as a cursor output parameter. Then, you can use the returned cursor for performing certain maintenance routines against your tables, such as running UPDATE STATISTICS , rebuilding indexes, and so on.

Listing 26.28 Creating a Stored Procedure That Returns a Cursor That Can Be Used to Operate Against Each Table in a Database

 CREATE PROC cur_tbl_names @tbl_cur CURSOR VARYING OUTPUT AS SET @tbl_cur = CURSOR LOCAL FORWARD_ONLY FOR    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES       WHERE TABLE_TYPE = 'BASE TABLE' OPEN @tbl_cur 

First, you can see that the parameter @tbl_cur is defined as CURSOR VARYING OUTPUT . The VARYING keyword is required if you want to return a reference to the cursor from the procedure. If cursor is declared as a local cursor within the stored procedure, it can still be passed back in an output parameter to a cursor variable, but it will only be accessible through the cursor variable, and not by name.

You now have a stored procedure that defines a cursor and returns it as an output parameter. You can write several batches (or stored procedures) that use the same cursor, and if you want to exclude tables, you modify the code only once in the cur_tbl_names stored procedure.

Listing 26.29 contains the code that calls your procedure and uses the cursor output parameter.

Listing 26.29 Calling the Procedure Defined in Listing 26.28

 DECLARE @tbls CURSOR DECLARE @table_name sysname EXEC cur_tbl_names @tbl_cur = @tbls OUTPUT FETCH NEXT FROM @tbls INTO @table_name WHILE @@FETCH_STATUS = 0 BEGIN     EXEC('DBCC DBREINDEX( ' + @table_name + ')')     FETCH NEXT FROM @tbls INTO @table_name END CLOSE @tbls DEALLOCATE @tbls 

The cursor can be closed using either the cursor variable or the declared cursor name. After the cursor is closed, you cannot fetch more rows from it until it is reopened, as shown in Listing 26.30.

Listing 26.30 Attempting to Fetch Rows from a Closed Cursor

 create proc cursor_proc @cursor CURSOR varying output as declare c1 cursor for select title, pubdate from titles set @cursor = c1 open c1 GO declare @curs CURSOR exec cursor_proc @cursor = @curs output fetch c1 fetch @curs close c1 fetch @curs go deallocate c1 go title                                                   pubdate ------------------------------------------------------- ----------------------- The Busy Executive's Database Guide                     1991-06-12 00:00:00.000 title                                                   pubdate ------------------------------------------------------- ----------------------- Cooking with Computers: Surreptitious Balance Sheets    1991-06-09 00:00:00.000 Server: Msg 16917, Level 16, State 2, Line 9 Cursor is not open. 

If a cursor has been declared in one instance and also assigned to a cursor variable, the cursor will exist until it is deallocated via the last remaining reference to the cursor. If the cursor has been closed, it can still be reopened using the remaining cursor reference(s), as shown in Listing 26.31. If the cursor has not been closed, the last deallocation of the cursor will automatically close it.

Listing 26.31 Deallocating a Cursor by Cursor Name and Cursor Variable

 declare @curs CURSOR exec cursor_proc @cursor = @curs output fetch c1 fetch @curs close c1          -- close via initial reference deallocate c1     -- deallocate via initial reference fetch @curs       -- cursor is not open at this point open @curs        -- reopen the cursor via the remaining reference fetch @curs       -- This fetch will work since cursor is now open again close @curs       -- close last reference to cursor deallocate @curs  -- deallocate last reference to cursor open @curs        -- cursor no longer exists go title                                                  pubdate ------------------------------------------------------ ----------------------- The Busy Executive's Database Guide                    1991-06-12 00:00:00.000 title                                                  pubdate ------------------------------------------------------ ------------------------ Cooking with Computers: Surreptitious Balance Sheets   1991-06-09 00:00:00.000 Server: Msg 16917, Level 16, State 2, Line 10 Cursor is not open. title                                                   pubdate ------------------------------------------------------- ----------------------- The Busy Executive's Database Guide                     1991-06-12 00:00:00.000 Server: Msg 16950, Level 16, State 2, Line 18 The variable '@curs' does not currently have a cursor allocated to it. 

For more information on using cursors and cursor variables in stored procedures, see Chapter 28.

Getting Information About Cursors

SQL Server 2000 provides some system functions and stored procedures to get information about cursors defined in a session.

The CURSOR_STATUS Function

The CURSOR_STATUS function returns the current state of a cursor or cursor variable. The syntax is as follows:

 CURSOR_STATUS('local',                '  cursor_name  '  'global', '  cursor_name  '  'variable',               '  cursor_variable  ') 

Table 26.25 lists the values returned by this function and what they mean.

Table 26.25. CURSOR_STATUS Return Values

Return Value	Cursor Name	Cursor Variable
1	The resultset has at least one row or is a dynamic cursor.	Cursor assigned to the variable is open and has at least one row or is a dynamic cursor.
	Cursor resultset is empty.	Cursor assigned to the variable is empty.
-1	Cursor is closed.	Cursor assigned to the variable is closed.
-2	N/A.	No valid cursor is assigned to the variable.
-3	Cursor does not exist.	Cursor variable doesn't exist or has not had a cursor assigned to it.

Dynamic cursors never return a value of 0 for CURSOR_STATUS because it is not known how many rows are in the cursor resultset until the rows are fetched.

Cursor Stored Procedures

SQL Server provides a set of system procedures you can use to get detailed information about cursors or cursor variables. Each of these stored procedures returns information as a cursor output parameter. The cursor procedures are listed in Table 26.26.

Table 26.26. Cursor System Procedures

Cursor Procedure	Information Returned
sp_describe_cursor @cursor_var OUTPUT, 'local' 'global' 'variable', cursor_name cursor_variable	Returns attributes about the specified cursor.
sp_cursor_list @cursor_var OUTPUT , 1 2 3	Returns attributes of all cursors open within specified scope. 1 = local, 2 = global, 3 = both. Essentially returns same information as sp_describe_cursor .
sp_describe_cursor_tables @cursor_var OUTPUT , 'local' 'global' 'variable', cursor_name cursor_variable	Returns information on the base tables referenced by a cursor.
sp_describe_cursor_columns @cursor_var OUTPUT, 'local' 'global' 'variable', cursor_name cursor_variable	Returns information on the columns in the cursor resultset.

When you use these system procedures, you'll typically return the output parameter into a local cursor variable and then fetch the results from the cursor variable.

The sp_describe_cursor procedure is probably the one you'll find most useful. Listing 26.32 demonstrates using the various cursor procedures to get information about the titles cursor you've been using throughout the chapter.

Listing 26.32 Using Cursor Stored Procedures

 declare titles_curs cursor for    select ytd_sales, price from titles    for update of price declare @ytd_sales int, @price money, @blah int open titles_curs go -- check to see if cursor is open print case cursor_status('global','titles_curs') when 1 then 'It's open'            else 'Sorry'            end go It's open /* get information about the titles_curs */ declare @curslist CURSOR exec sp_describe_cursor @curslist output, 'global', 'titles_curs' if cursor_status('variable','@curslist') = 1   -- make sure you have a valid cursor from the proc begin     fetch Next from @curslist     while @@fetch_Status = 0     begin         fetch Next from @curslist     end end close @curslist deallocate @curslist go reference_name                                                          cursor_name cursor_scope status      model concurrency scrollable open_status cursor_rows fetch_status column_count row_count    last_operation cursor_handle ------------------------------------------------------------------------------ -------------------------------------------------- --------------------------- ----------------------------------------------------------------------------- ------------------------ ------------ ----------- ----- ----------- ----------  ----------- ------------ ------------ ------------ ------------ ------------ -- ------------- titles_curs   titles_curs                         2           1     3           3          0           1  -1   -9            2            0              1     180150029 reference_name          cursor_name  cursor_scope status      model concurrency scrollable open_status cursor_rows fetch_status column_count row_count    last_operation cursor_handle -------------------------------------------------------------------------------- ------------------------------------------------ ----------------------------- ------------------------------------------------------------------------------ --------------------- ------------ ----------- ----- ----------- ---------- --- -------- ------------ ------------ ------------ ------------ -------------- -- /* Get the same information for all open cursors */ declare @curslist CURSOR exec sp_cursor_list @curslist output, 3 if cursor_status('variable','@curslist') = 1   -- make sure you have a valid cursor from the proc begin     fetch Next from @curslist     while @@fetch_Status = 0     begin         fetch Next from @curslist     end end close @curslist deallocate @curslist go reference_name cursor_name cursor_scope status      model concurrency scrollable open_status cursor_rows fetch_status column_count row_count    last_operation cursor_handle ---------------------------------------------------------------------------- ---------------------------------------------------- ----------------------- ---------------------------------------------------------------------------- ----------------------------- ------------ ----------- ----- ----------- --- ------- ----------- ------------ ------------ ------------ ------------ --- ----------- ------------- titles_curs         titles_curs                            2           1     3           3          0           1  -1   -9            2            0              1     180150029 reference_name cursor_name cursor_scope status      model concurrency scrollable open_status cursor_rows fetch_status column_count row_count    last_operation cursor_handle ----------------------------------------------------------------------------- --------------------------------------------------- ------------------------ ---------------------------------------------------------------------------- ---------------------------- ------------ ----------- ----- ----------- ---- ------ ----------- ------------ ------------ ------------ ------------ ----- --------- ------------- /* Get information about the tables referenced by the titles_curs cursor */ declare @curslist CURSOR exec sp_describe_cursor_tables @curslist output, 'global', 'titles_curs' if cursor_status('variable','@curslist') = 1   -- make sure you have a valid cursor from the proc begin     fetch Next from @curslist     while @@fetch_Status = 0     begin         fetch Next from @curslist     end end close @curslist deallocate @curslist go table_owner table_name optimizer_hint lock_type server_name objectid    dbid        dbname ---------------------------------------------------------------- ----------------------------------------------------------------  --------------------------------------------------------------- ---------------------------------------------------------------- - -------------- --------- ------------------------------------- ----------------------------------------------------------------- -------------------------- ----------- ----------- ------------- ----------------------------------------------------------------- -------------------------------------------------- dbo titles                                                   0         0 RRANKINSA20P\RRANKINSSQL2000  1781581385           8 bigpubs2000 table_owner table_name optimizer_hint lock_type server_name objectid    dbid        dbname ----------------------------------------------------------------------- --------------------------------------------------------- ------------- ----------------------------------------------------------------------- ------------------------------------------ -------------- --------- --- ----------------------------------------------------------------------- ------------------------------------------------------ ----------- ---- ------- ---------------------------------------------------------------- ---------------------------------------------------------------- /** Get information about the columns returned by the specified cursor **/ declare @curslist CURSOR exec sp_describe_cursor_columns @curslist output, 'global', 'titles_curs' if cursor_status('variable','@curslist') = 1   -- make sure you have a valid cursor from the proc begin     fetch Next from @curslist     while @@fetch_Status = 0     begin         fetch Next from @curslist     end end close @curslist deallocate @curslist go column_name ordinal_position column_characteristics_flags column_size data_type_ sql column_precision column_scale order_position order_direction hidden_ column columnid    objectid    dbid        dbname -------------------------------------------------------------------------- ------------------------------------------------------ ---------------- -- -------------------------- ----------- ------------- ---------------- ---- -------- -------------- --------------- ------------- ----------- -------- --- ----------- ---------------------------------------------------------- ---------------------------------------------------------------------- ytd_sales 0                            6           4            56               10          0              0 NULL                        0           8 1781581385           8 bigpubs2000 column_name ordinal_position column_characteristics_flags column_size data_type_ sql column_precision column_scale order_position order_direction hidden_ column columnid    objectid    dbid        dbname -------------------------------------------------------------------------- ------------------------------------------------------ ---------------- -- -------------------------- ----------- ------------- ---------------- ---- -------- -------------- --------------- ------------- ----------- -------- --- ----------- ---------------------------------------------------------- ---------------------------------------------------------------------- price 1                           22           8            60               19         4              0 NULL                        0           5  1781581385            8 bigpubs2000 column_name ordinal_position column_characteristics_flags column_size data_type_ sql column_precision column_scale order_position order_direction hidden_column columnid    objectid    dbid        dbname ------------------------------------------------------------------------------ -------------------------------------------------- ---------------- ---------- ------------------ ----------- ------------- ---------------- ------------ --- ----------- --------------- ------------- ----------- ----------- -----------  --------------------------------------------------------------------------- ----------------------------------------------------- 

Because the results from each of these procedures is a cursor resultset, you could fetch the results into local variables if you needed to use the cursor name or other information returned in your SQL code. For example, you could check to see what cursor is open and conditionally branch to the SQL code or a stored procedure that is set up to fetch data from that cursor. Or, you might want to know what columns are coming back from the cursor so you know what values you will be fetching.

API Cursors

So far, this chapter has examined how you can use cursors through T-SQL. The most common use of cursors, however, is through an application programming interface (API). Each API has calls or methods for defining cursor capabilities.

In fact, you could say that all results from SQL Server are returned through a cursor. The simplest case is that the client retrieves rows one at a time, scrolling forward (reading from the input buffer) through the resultset. This is called a default resultset.

If you need more advanced scrolling capabilities, part of the resultset must be cached somewhere, so you can use a key when searching for the previous row. This caching can occur at the client or at the server.

Client cursors are implemented at the client side. ODBC or OLE-DB caches the necessary information. No cursor calls are sent to SQL Server. Client cursors are useful if any of the following conditions exists:

• You have a slow network connection to the SQL Server.

• The resultset doesn't have many rows, or you will navigate through a major part of the resultset.

• You will allow the user to interact rapidly through the cached resultset.

A Web-based application is a good example of where client cursors can be valuable. For example, a SQL statement is sent to SQL Server to retrieve a number of customer names. This resultset is buffered at the client side. When the user chooses a customer, another SQL statement is executed, and customer details are presented in the Web browser.

By default, server-side cursors are used in the programming APIs, but the programmer can choose to use client cursors instead.

You can also choose the number of rows to be returned with each fetch operation sent to SQL Server ( fat cursors) to minimize network traffic. This is not possible with Transact-SQL cursors; it would not make sense anyway because all processing is done at the server.

API server cursors are implemented through the API cursor support in SQL Server. SQL Server has a number of sp_cursor extended-stored procedures used by ODBC and OLE-DB, which implements API server cursors. The following is a list of the cursor procedures used by API cursor libraries:

• sp_cursoropen ” Defines the SQL statement to be associated with the cursor and the cursor options, and then populates the cursor.

• sp_cursorfetch ” Fetches a row or block of rows from the cursor.

• sp_cursorclose ” Closes and deallocates the cursor.

• sp_cursoroption ” Sets various cursor options.

• sp_cursor ” Requests positioned updates.

• sp_cursorprepare ” Generates an execution plan for the cursor but does not create the cursor.

• sp_cursorexecute ” Creates and populates a cursor from the execution plan created by sp_cursorprepare .

• sp_cursorunprepare ” Discards the execution plan from sp_cursorprepare .

Specifying the procedures directly in an application is not supported. I've included them here so you'll know what they are if you see them show up in a SQL Profile trace.

Your application cannot use API server cursors when executing the following statements:

• Batches or stored procedures that return multiple resultsets

• SELECT statements that contain COMPUTE , COMPUTE BY , or INTO clauses

• An EXECUTE statement that references a remote stored procedure

When to Use Cursors

Application performance can sometimes be slow due to the improper use of cursors. Always try to write your T-SQL code so SQL Server can perform what it is good at: set-based operations. It makes little sense to have an advanced RDBMS and only use it for one-row-at-a-time retrievals.

The cursor example discussed earlier in this section can be performed with a single UPDATE statement using the CASE expression as shown in Listing 26.33.

Listing 26.33 The titles Cursor Example Performed with One UPDATE Statement Instead

 update titles    set price = case when ytd_sales < 500 then price *.75                     when price >  then price * .90                     else price * 1.15                end 

The advantage with this approach is a significant performance improvement, not to mention much cleaner and simpler code. I tested the performance of the single update versus the cursor on a Pentium III 700Mhz machine with 256MB of memory using the bigpubs database available on the CD. The titles table contains 537 rows. The single update statement completed in 80 milliseconds (ms). The cursor required 413ms. Although both of these completed in subsecond response time, consider that the cursor took five times longer to complete than the single update. Factor that out over hundreds of thousands or millions of rows, and you are talking a significant performance difference.

Why is the cursor so much slower? Well for one thing, a table scan performed by an UPDATE , DELETE , or SELECT uses internal, compiled C code to loop through the resultset. A cursor uses interpreted SQL code. In addition, with a cursor, you are performing multiple lines of code per row retrieved. The titles cursor example is a relatively simple one, only performing one or two conditional checks and a single update per row, but it is still five times slower. Adding just a single assignment select into the cursor loop added an average of 10ms to the time it took for the cursor to complete. Because of the overhead required to process cursors, set-oriented operations will typically run much faster, even if multiple passes of the table are required.

Although set-oriented operations are almost always faster than cursor operations, the one possible disadvantage of using a single update is locking concurrency. Even though a single update runs faster than a cursor, while it is running, the single update might end up locking the entire table for an extended period of time. This would prevent other users from accessing the table during the update. If concurrent access to the table is more important than the time it takes for the update to complete, you might want to use a cursor. A cursor only locks the data a row at a time instead of locking the entire table (as long as each row is committed individually and the entire cursor is not in a transaction).

Another situation in which you might want to consider using cursors is for scrolling applications when the resultsets can be quite large. Consider a customer service application. The customer representative might need to pull up a list of cases and case contacts associated with a customer. If the resultsets are small, you can just pull the entire resultset down into a list box and let the user scroll through them and not need to use a cursor. However, if thousands of rows of information are likely, you might want to pull back only a block or rows at a time, especially if the user only needs to look at a few of the rows to get the information he needs. It probably wouldn't be worth pulling back all that data across the network just for a few rows.

For this type of situation, you might want to use a scrollable API server cursor. This way, you can retrieve the appropriate number of rows to populate the list box and then use the available scrolling options to quickly fetch to the bottom of the list using the LAST or ABSOLUTE n options, or go backward or forward using the RELATIVE option.

NOTE

Be careful using this approach in a multitier environment. Many multitier architectures include a middle data layer that often uses connection sharing for multiple clients , and the users will typically be assigned any available connection when they need to access SQL Server. Users do not necessarily use the same connection each time. Therefore, if a user created a cursor in one connection, the next time the user submitted a fetch through the data layer, he might get a different connection and the cursor will not be available.

One solution for this problem is to go back to retrieving the entire resultset down to the client application. Another possible solution is to use a global temp table as a type of homemade insensitive cursor to hold the resultset and grab the data from the global temp table in chunks as you need it. For the temp table approach, make sure a sequential key is on the table so you can quickly grab the block of rows that you need. Be aware of the potential impact on tempdb performance and the size requirements of tempdb if the resultsets are large and you have many concurrent users.

As a general rule, use cursors only as a last resort when no set-oriented solution is feasible . If you have decided that a cursor is the appropriate solution, try to make it as efficient as possible by limiting the number of commands to be executed within the cursor loop as much as possible. Also, try to keep the cursor processing on the server side within stored procedures. If you will be performing multiple fetches over the network (such as to support a scrolling application), use an API server cursor. Avoid using client-side cursors that will be performing many cursor operations in the client application. You will find your application making excessive requests to the server and the volume of network roundtrips will make for a sloth-like application.