Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 24 -
Creating and Using Cursors

Cursors process a group of records one-by-one, instead of using more traditional batch processing approaches.
The location from which a cursor is being operated can affect performance significantly. Find out what this impact is and how you can control it in your systems.
A cursor does not automatically improve performance. Find out the tips and techniques that you can apply to make the cursor work best in your application environment.

Perhaps the biggest feature that Microsoft added to SQL Server 6.0 is back-end, or server-side, cursor support. Cursors provide a way to manipulate data in a set on a row-by-row basis rather than the typical use of SQL commands that operate on all the rows in the set at one time.

Specifically, Microsoft added a full implementation of back-end cursors, which are cursors that the database manages. These cursors have an easy access method from front-end application development tools and platforms such as SQLWindows and PowerBuilder. You could use cursors in previous releases of SQL Server, but those cursors were provided by the DBLibrary layer, not the server; for this reason, Microsoft refers to these new cursors as back-end cursors.

Cursors provide a way to do result-set processing inside the server without the need for a client program to manage the data sets on which you are working. For example, before SQL Server 6.0, it was difficult to write a fast- performing application that had to perform multiple actions on a set of data. The reasons for this difficulty were that you had to return each row in the data to the front end, and that the client application was responsible for initiating further activity on each row. Cursors provide a way for advanced Transact-SQL stored procedures to do all this processing without needing to return to the client.


CAUTION: Cursors can be terribly hard on your application's performance. They are, by their very nature, much slower to execute and respond than standard set-based operations. Make certain that you've exhausted set-based operations before you look to cursors, and make sure that you're not only considering their ease of use, but also the performance issues inherent their use.

Distinguishing Between Front-End and Back-End Cursors

With SQL Server, two types of cursors are available for use in an application: the front-end, or client, cursors, and the back-end, or server, cursors. These two types of cursors are quite different, so distinguishing between them is important.


NOTE: Microsoft refers to back-end cursors, or cursors that the creates and manages, as server cursors. To avoid any confusion from this point on, unless this book specifically refers to cursors on the client or server, the term cursor refers to a cursor created in the database server. n

When writing an application, you'll often find that you need to perform a given operation on a set of data. To perform such set-based operations, you usually can use an Update statement when you need to change data values, or a Delete statement when you need to remove data values. These set-based operations often provide great flexibility in an application, if a Where clause can appropriately define the required tasks.

Suppose that in the Pubs database, you want to change the ZIP code to 94024 for all authors who live in Menlo Park. To do so, you can use the simple Update statement shown in Listing 24.1.

Listing 24.1 24_1.SQL--Using Update to Change a ZIP Code

Update      AUTHORS
Set   ZIP = `94024'
Where City = `Menlo Park'
Go

On the other hand, what if you need to perform different kinds of operations on a set of data? Two solutions are possible: You can perform multiple operations on exclusive sets, or you can get the whole set of data and, based on values in the set, perform the required operations. This second solution is the concept behind cursor-based processing.

Relying on the set-based updates and deletes can be inefficient, because your updates might end up hitting the same row more than once. Alternatively, you can create a view of data in the database called a cursor.

One of the best advantages of cursor processing is that you can perform conditional logic on a particular row of data in a set independently of the other rows that might be in a set. Effectively, you are issuing commands or SQL on single-row data sets. Complex applications often require this granularity of processing. In addition, this granularity offers several benefits:


See the section "Updating Rows" in Chapter 8, "Adding, Changing, and Deleting Information in Tables," for more information on updates.

Understanding Client Cursors

Before releasing SQL Server 6.0, Microsoft realized that its customers needed to be able to process data and to scroll backward and forward through a result set. Customers needed this scrolling functionality to support complex applications that users needed for browsing data fetched from the database.

At the time, Microsoft couldn't incorporate the server-based cursors that some of the other vendors supported, and so chose to mimic some of these cursors' behavior in Microsoft's client application programming interface (API) to the SQL Server database, DBLibrary.


NOTE: Microsoft inherited the DBLibrary client interface from Sybase to interact with the SQL Server database. DBLibrary is a set of commands and functions that you can execute in C to perform operations on the database. With SQL Server 6.0 and later releases, Microsoft changed its preferred interface to the database to be that of Open Database Connectivity (ODBC). For more discussions on interfacing with the database from client applications and programming languages, see Chapters 22, "Developing Applications to Work with SQL Server," and 23, "Understanding SQL Server and the Internet."

To achieve this functionality, Microsoft added client cursors to the data sets on the client side.

These cursors work by having DBLibrary interact with the database how it normally does: by fetching data from the tabular data stream (TDS) as quickly as the client requests. TDS is the method of communication that DBLibrary uses to fetch data from the database. Typically, DBLibrary discards any data that it fetches from the database, and then gives the data to the client application, relying on the client to perform any additional work. With cursors activated, DBLibrary caches these records itself until the client cancels the cursor view on the data.

This caching has several limitations:

Client cursors were just a stopgap measure until Microsoft could complete the real work of server cursors. Server cursors provide all the same benefits of client cursors without any of the overhead or limitations. Aside from backward-compatibility issues, there are few good reasons to use client cursors in an SQL Server 6.5 application.

When used, server cursors usually have five states, as described in Table 24.1.

Table 24.1 The States of Existence of SQL Server Cursors

State Explanation
DECLARE At this point, SQL Server validates that a valid cursor will populate the query. SQL Server creates in shared memory a structure that has the definition of the cursor available for compilation at the OPEN phase.
OPEN SQL Server begins to answer the DECLARE statement by resolving the query and fetching row IDs into a temporary workspace for the use of the client, should it decide to fetch the rows that this cursor has identified.
FETCH In this state, the cursor returns the data so that any required activity can be performed.
CLOSE SQL Server closes the previously opened cursor and releases any locks that it might be holding as a result of opening it.
DEALLOCATE SQL Server releases the shared memory used by the DECLARE statement, no longer permitting another process to perform an OPEN on it.

Using SQL Server Cursors

To use a cursor in SQL Server, you need to follow the states described in Table 15.1. This section explains the steps that you must follow to use a cursor effectively in your applications.

You first must declare the cursor. Then you can open the cursor and fetch from it. During a cursor's fetch phase or state, you can perform several different operations on the cursor's currently active row. When you finish working with a cursor, you must close and deallocate it so that SQL Server does not waste resources managing it any further.

Declaring a Cursor

Declaring a cursor is quite similar to requesting data using a standard Select statement. Note that the Select statement that you use to declare a cursor can't include any of the Transact-SQL extensions such as COMPUTE, COMPUTE BY, or SELECT INTO.

The syntax for declaring a cursor is as follows:

DECLARE name_of_cursor [INSENSITIVE] [SCROLL] CURSOR
FOR Select_Statement
[FOR {READ ONLY | UPDATE [OF Column_List]}]


NOTE: Because cursors must fetch row values into variables inside the stored procedure or command batch, you can't use the asterisk (*) in your Select statement. In the data tables, you must use named columns that correspond one-to-one with the variables used in the FETCH clause. n

The options for the Transact-SQL command DECLARE CURSOR are as follows:


CAUTION: Be careful when using the INSENSITIVE keyword in defining a cursor. An application that uses this keyword might encounter problems of inaccurate data if the application has high transaction loads on the underlying table or tables on which the cursor is being opened. If you are writing a time-driven application, however, INSENSITIVE cursors are required. An example of this might be "tell me what our balance sheet position is as of right now, regardless of outstanding or in-process transactions."

Listing 24.2 shows a basic cursor declared to fetch data from a single table (EMPLOYEE) in the Pubs database.

Listing 24.2 24_2.SQL--A Cursor Declared to Retrieve Information from the EMPLOYEE Table

Declare Cur_Empl Cursor
For   Select EMP_ID,    LNAME,
            JOB_ID,     PUB_ID
      From  EMPLOYEE
      Order By EMP_ID
Go

The Cur_Empl cursor, as shown in Listing 24.2, makes an application no more flexible than a simple SELECT on the data would. However, if the application requires absolute row positioning, as shown in Listing 24.3, you can add the SCROLL keyword to the Declare statement to make the cursor behave much more differently than a table SELECT.

Listing 24.3 24_3.SQL--A Scrollable Cursor Declared to Fetch from the EMPLOYEE Table

Declare Cur_Empl_Scrollable SCROLL Cursor
For   Select EMP_ID,    LNAME,
            JOB_ID,     PUB_ID
      From  EMPLOYEE
      Order By EMP_ID
Go

Opening a Cursor

After you declare a cursor, SQL Server reserves handles for its use. To use a cursor and fetch data from it, you must open the cursor using the following syntax:

Open Cursor_Name

In the preceding examples, the code required to open the cursor would have been either one of the following:

Open Cur_Empl

Open Cur_Empl_Scrollable

When a cursor is opened, SQL Server resolves any unknown variables with their current state. If you declare a cursor with a variable in the Where clause and then open the cursor, the value used to resolve the query is the value that the variable holds when you open the cursor, as shown in the example in Listing 24.4.

Listing 24.4 Declaring and Opening a Cursor

Declare     @nHighJobID integer,
            @nLowJobID  integer
Declare Cur_Empl_Where Cursor
For   Select      LNAME, FNAME
From  EMPLOYEE
Where JOB_ID Between @nLowJobID And @nHighJobID
/* Note that if the cursor were to be opened now,
probably no data would be returned because the values
of @nLowJobID and @nHighJobID are NULL */
/* Now we set the values of the variables */
Select      @nLowJobID = 3,
      @nHighJobID = 10
/* open the cursor now */
Open Cur_Empl_Where
...


NOTE: You can determine how many rows the cursor found. To do so, evaluate @@Cursor_Rows. If the number of rows is negative, the cursor hasn't yet determined the total number of rows. Such might be the case if the cursor is still serially fetching the rows to satisfy the cursor definition. If the number of rows is zero, there are no open cursors, or the last cursor that was open was closed or deallocated.

Fetching a Cursor

After you open a cursor, you can fetch data from it. Unless you declare a cursor with the SCROLL keyword, the only kind of fetching permitted is serially or sequentially through the result set.

The syntax for the FETCH statement is as follows:

FETCH [[NEXT | PRIOR | FIRST | LAST |
       ABSOLUTE n/@nvar | RELATIVE n/@nvar ]
FROM] cursor_name
[INTO @variable_name1, @variable_name2]

The options for the Transact-SQL command FETCH are as follows:


NOTE: In SQL Server 6.5, Microsoft has enhanced the syntax of the FETCH ABSOLUTE and FETCH RELATIVE statements by enabling you to substitute @ variables for n. The only variables that you can use are those of types int, smallint, or tinyint.

Closing a Cursor

Closing a cursor releases any resources and locks that SQL Server might have acquired while the cursor was open. To close a cursor, use the following syntax:

CLOSE cursor_name

A closed cursor is available for fetching only after it's reopened.

Deallocating a Cursor

Cursors keep track of your location in the database and maintain an active, open structure of pointers to the next and previous row information.

Deallocating a cursor completely removes any data structures that SQL Server was holding open for a given cursor. Deallocating a cursor differs from closing a cursor; after you deallocate a cursor, you can no longer open it.

To deallocate a cursor, use the following syntax:

DEALLOCATE cursor_name

An Example of Using Cursors

The previous sections discussed all the separate elements that you use to work with cursors in SQL Server. This section shows how you put together all the elements.

Listing 24.5 is an example of the use of cursors in action. Refer to the comments in the script (placed between /* and */) to get a good understanding of what the cursors are doing. However, to summarize, the following steps are taken:

1. Drop the procedure and recreate it if necessary. This step ensures that the current procedure is the one that executes. Note also that if you have SQL Enterprise Manager generate the code for a stored procedure, any such statements that drop the existing stored procedure are generated automatically.

2. Create a cursor to find the information that you need. Doing so allocates the memory structures and sets up the cursor so that you can retrieve information from it in later logic.

3. Open the cursor and retrieve the first sets of information from it.

4. In the loop, retrieve information from the cursor until the logic to determine books on order completes. Return the results to the calling application.

5. End the procedure, cleaning up on the way out.

Listing 24.5 24_4.SQL--Using Cursors to Process the STORES Table in the Pubs Database

/* In this example, we will be working with the stores table
of the pubs database.
To illustrate the cursors most easily, we will create a stored
procedure, that when executed:
- declares,
- opens,
- fetches, and
- processes
the data returned from a cursor. */
/* First we drop the procedure if it exists. */
If exists( select object_id( `proc_Stores' ) )
     Drop Procedure proc_Stores
Go
/* Step 0: Declare the procedure. */
Create Procedure proc_Stores
As
/* Step 1: Declare some working variables. */
Declare     @nOrderCount     integer,
     @nSQLError      integer,
     @nStorCount     tinyint,
     @sState          char(2),
     @sStorId     char(4),
     @sStorName     varchar(40),
     @sCity          varchar(20)
/* Step 2: Turn off result counting.
Turns off unnecessary "0 rows affected messages" showing on the front-end */
Set NoCount On
/* Step 3: Declare the cursor that is going to find all
the data.
This step causes SQL Server to create the resource
structures required to manage the cursor. */
Declare Cur_Stores Cursor
For     Select      STOR_ID,     STOR_NAME,
          CITY,          STATE
     From     STORES
     Order By     STOR_ID
/* Step 4: Open the cursor.
This step causes SQL Server to create the initial result set
and prepare the data for returning to the fetching process. */
Open     Cur_Stores
/* Step 5: Perform the first fetch.
Fetch data from the cursor into our variables for processing
and evaluation. */
Fetch     Cur_Stores
Into     @sStorId,     @sStorName,
     @sCity,          @sState
/* Step 6: Initialize counters. */
Select     @nStorCount = 0
/* Step 7: Fetch and process loop.
Process the data while the system variable @@Fetch_Status is = 0
(meaning that a row has been fetched from the cursor) */
While @@Fetch_Status = 0
Begin
     /* Step 8: Increment counter */
     Select      @nStorCount = @nStorCount + 1
     /* Step 9: Do a quick operation to determine books on order */
     Select     @nOrderCount = Sum(QTY)
     From     SALES
     WHERE     STOR_ID = @sStorID
     /* Step 10: Return a result set to the front-end so that it knows
     what is happening */
     Select     "Store ID" = @sStorId,    
          "Store Name" = @sStorName,
          "# Books on order" = @nOrderCount
     /* Step 11: Continue fetching.
     If no rows are found, then @@Fetch_Status will be set to a value other
     than zero, and the looping will end. */
         
     Fetch     Cur_Stores
     Into     @sStorId,     @sStorName,
          @sCity,          @sState
End
/* Step 12: Clean up - deallocate and close the cursors.
Note that for a stored procedure, this is really unnecessary because
the cursor will no longer exist once the procedure finishes execution.
However, it is good practice to leave the procedure cleaned up */
Close     Cur_Stores
Deallocate Cur_Stores
/* Step 13: Send a totaling result.
Send total count of employees to front-end */
Select "Total # of Stores" = @nStorCount
/* Step 14: Turn on counting again */
Set NoCount On
/* Step 15: End procedure */
Return 0
Go
/* Now we execute it to see the results. */
Execute proc_Stores
Go

When you run this program, the output looks like the following:

Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
6380     Eric the Read Books                                     8
Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
7066     Barnum's                                              125
Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
7067     News & Brews                                           90
Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
7131     Doc-U-Mat: Quality Laundry and Books                  130
Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
7896     Fricative Bookshop                                     60
Store ID Store Name                               # Books on order
-------- ---------------------------------------- ----------------
8042     Bookbeat                                               80
Total # of Stores
----------------
                6


TIP: Sorting variables alphabetically in large procedures makes finding the variables much easier. In addition, you can sort the variables by datatype as well, so that finding them is even easier. This sorting occurs automatically if you prefix variables with a datatype indicator such as s for strings, n for numbers, or dt for dates and times.

Using Nested Cursors

A stored procedure can have multiple layers of cursors that you use to provide flexible result-set processing. An example of having more than one cursor active is when you're opening a cursor, as shown earlier in the Cur_Empl example. In addition to the cursor that you've already reviewed, you can add nested cursors to impose some additional conditional logic and perhaps open a second cursor to perform additional work with the data set.

Listing 24.6 shows some possibilities with nested cursors and provides an example of retrieving the employee record and then using that information to retrieve detail records for the employee.

Listing 24.6 24_5.SQL--Using Nested Cursors

Create Procedure Maintain_Employees
As
/* First declare variables that are going to
be required in this procedure */
Declare     @dtPubDate   datetime,
            @nEmplCount  smallint,
            @nEmplID     empid,
            @nFirstHalf  smallint,
            @nRowCount   integer,
            @nSecondHalf integer,
            @nSQLError   integer,
            @nYtdSales   integer,
            @sLName      varchar(30),
            @sPubID      char(4),
            @sLastType   char(12),
            @sType       char(12)
/* Now declare the cursors to be used.
Note that because variables are used in the
where clause on the second cursor, it is not
required that the second cursor be declared inside the first.
Take advantage of this functionality so that unnecessary
declaring of cursors does not take place (this will
save resources on the server). */
Declare Cur_Empl Cursor
For   Select EMP_ID,    LNAME,
             PUB_ID
      From   EMPLOYEE
      Order By EMP_ID
Declare Cur_Titles Cursor
For   Select  TYPE,   PUBDATE, YTD_SALES
      From    TITLES
      Where   PUB_ID = @sPubID
Order By TYPE
/* Open the outer cursor and fetch the first row */
Open  Cur_Empl
Fetch Cur_Empl
Into  @nEmplID,   @sLName,
      @sPubID
/* Initialize counters */
Select      @nEmplCount = 0
While @@Fetch_Status = 0            /* Fetch only while there are rows left */
Begin
      /* increment counter */
      Select @nEmplCount = @nEmplCount + 1
      /* Return a result set to the front-end so that it knows
      what is happening */
      Select      @nEmplID,   @sLName
      If @sLName < `D'  /* Skip all the D's by using a GOTO */
            Goto Fetch_Next_Empl
      /* Now open inner cursor and count the different types
      of books for this employee's publisher */
      Open Titles
      Fetch Titles
      Into  @sType, @dtPubDate, @nYtdSales
      /* Reset totals */
      Select @nFirstHalf = 0,
            @nSecondHalf = 0,
            @sLastType = NULL
      While @@Fetch_Status = 0
      Begin
            If @sType != @sLastType AND @sLastType != NULL
            Begin
                  /* Send back a total record to the front-end */
                  Select @sLastType, @nFirstHalf, @nSecondHalf
                  /* Reset totals */
                  Select @nFirstHalf = 0,
                        @nSecondHalf = 0
            End
            If @dtPubDate <= `6/30/95'
                  Select @nFirstHalf = @nFirstHalf + @nYtdSales,
                        @sLastType = @sType
            Else
                  Select @nSecondHalf = @nSecondHalf + @nYtdSales,
                        @sLastType = @sType
            Fetch Titles
            Into  @sType, @dtPubDate, @nYtdSales
      End
      Fetch_Next_Empl:        /* Label to skip inner loop */
      Fetch Cur_Empl
      Into  @nEmplID,   @sLName,
            @sPubID
End
/* Deallocate and close the cursors. Note that for a stored
procedure, this is really unnecessary because the cursor
will no longer exist once the procedure finishes execution.
However, it is good practice to leave the procedure cleaned up. */
Close Cur_Empl
Deallocate Cur_Empl
Deallocate Cur_Titles
/* Send total count of employees to front-end */
Select @nEmplCount
/* End proc */
Return 0


TIP: SQL Server treats object names case-insensitively, regardless of the sort order defined for the server. You can take advantage of this and make your code easy to read by using upper- and lowercase emphasis when possible.

However complex this example might seem, using cursors is really not too difficult if you follow the basic steps outlined in the previous examples and throughout this chapter.

Processing Cursors from Front-End Applications

A key consideration of using cursors in an application is how they can be accessed from front-end programming tools such as SQLWindows or PowerBuilder.

If the cursor returns a single set of data, which is the most common type, most front-end application languages cannot distinguish the data from that returned by a normal SELECT statement. Typically, the tool has a function for executing SELECT statements. This function is designed to work with a single set of data and, therefore, probably will work fine with stored procedures or cursors that return a single result set.

Most tools provide special functions for referencing data that comes from a cursor if the cursor and its associated processing returns more than one result set. A common construction might be something like this SQLWindows example snippet:

...
Call SqsExecuteProc( hSql, `proc_Stores', gsResults )
While SqsGetNextResults( hSql, gsResults )
While SqlFetchNext( hSql, nReturn )
...

The execution of the stored procedure is followed by looping that forces the return of results to the front-end and then the fetching of the data in each result.

Most programming languages have similar functionality available. Whatever programming language you choose for your development should be capable of supporting anything that SQL Server can return. For a more detailed discussion on several client/server programming tools, refer to Chapter 25, "Accessing SQL Server Databases Through Front-End Products."

Reality Check

Cursors are, at first glance, a great feature for your applications. Indeed, cursors offer good funcionality for your application if you need to provide database-browsing capabilities but don't want to develop this type of functionality on your application's client side.

Because of their inherent functionality, cursors bring quick payback on the development cycle, but can harm the application's performance. Although the effect depends on your implementation, including the hardware on which the server is running, cursors can have a significant negative impact on your application's overall throughput.

From Here...

In this chapter, you learned about SQL Server's new server-based cursors and how you can use them to provide much more processing power to your applications without having to return to the client for help. According to Sybase's performance-tuning group, whose understanding of SQL Server's cursors is quite intimate, server cursors provide greater performance than all other results-set processing mechanisms, including embedded SQL in a C application running on the server.

The reason for this high performance is that cursors don't require any networking. Unless you can't model in stored procedures the type of work that you're doing--which might be the case with arrays--you should move all batch operations to cursor-based procedures running on the server.

From here, you can refer to the following chapters to implement what you've learned:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.