Chapter 15 - Creating and Using Cursors
- Learn how to create and use cursors - Cursors are used to process a group of records on a one-by-one basis
- Learn the difference between front-end and back-end cursors. - Where a cursor is being operated from can have a significant effect on performance.
- How to optimize cursor use in an application - A cursor does not automatically yield performance gains. It must be placed and used appropriately.
In SQL Server 6.0, perhaps the biggest feature that Microsoft added to the release was back-end, or server, cursor support. Cursors are a way of manipulating data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. Specifically, Microsoft added a full implementation of back-end cursors—cursors managed by the database and that have an easy access method from front-end application development tools such as SQLWindows and PowerBuilder or other development platforms. In referring to back-end, the distinction is made so it's understood that SQL Server always could have cursors, but these were provided by the DBLibrary layer, not the server.
Cursors provide a way of doing result-set processing inside the server without the need for a client program to manage the data sets being worked on. 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. This was because each row in the data would need to be sent back to the front end and the client application would be 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.
Distinguishing Between Front-End and Back-End Cursors
As just mentioned, with SQL Server, two types of cursors are available for use in an application: front-end (client) cursors and back-end (server) cursors. They are two very distinct concepts, and it's important to be able to distinguish between them.
Microsoft refers to back-end cursors, or cursors that are created and managed by the database, as server cursors. To avoid any confusion from this point on, unless the text specifically refers to cursors on the client or server, assume that any text referencing the term cursor is describing a cursor that's created in the database server.
When writing an application, you'll often find that you need to perform a given operation on a set of data. This set-based operation can normally be performed by using an Update statement when it's necessary to change data values, or a Delete statement when it's necessary to remove data values. These set-based operations often provide great flexibility in an application—provided that the tasks that need to be performed can appropriately be defined by a Where clause. Suppose that you wanted to change the ZIP code for all those authors in the Pubs database that lived in Menlo Park to be 94024. A simple Update could be used, as Listing 15.1 shows.
Listing 15.1 15_1.SQL—Using Update to Change a ZIP Code
Update AUTHORS
Set ZIP = '94024'
Where City = 'Menlo Park'
Go
But what if you need to do different kinds of operations on a set of data? There are two possible solutions: you can perform multiple operations on exclusive sets, or you can get the whole set of data and, based on values in it, perform the required operations. This second solution is the concept of cursor-based processing.
Relying on the set-based updates and deletes can be inefficient. This is because your updates may end up hitting the same row more than once. It's possible to 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 may be in a set. Effectively, you're issuing commands or SQL on single-row data sets. This granularity of processing is often required in complex applications, and has many benefits, as follows:
- Performance. Set-based operations tend to use more server resources, compared with cursor operations.
- Better transaction control. When you're processing sets of data, you can control what happens to any given row independent of the others.
- Special syntax. WHERE CURRENT OF cursors allow positioned Updates and Deletes that apply to the row now being fetched and directly hit the table row without the need of an index.
- Efficiency. When you're performing a number of operations on a large data set, such as calling multiple stored procedures, it's more efficient for the database to process the data doing all actions per row (because data is kept in memory caches) rather than perform each task serially on the entire data set.
See the section entitled Updating Rows in Chapter 7.
Understanding Client Cursors
Before 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 instead chose to mimic some of their behavior in their client API to the SQL Server database—DBLibrary.
DBLibrary is a client interface that Microsoft inherited from Sybase to interact with the SQL Server database. DBLibrary is a set of commands and functions that can be executed 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 ODBC. For more discussions on interfacing with the database from client applications and programming languages, see Chapters 21 and 22.
To achieve this functionality, Microsoft added cursors to the data sets on the client side—client cursors.
These cursors work by having DBLibrary interact with the database as normal, fetching data from the tabular data stream (TDS) as quickly as the client requests. The TDS is the method of communication that DBLibrary uses to fetch data from the database. Typically, DBLibrary will discard any data that has been fetched from the database, and then fetched to the client application—relying on the client to perform any additional work. With cursors activated, DBLibrary will cache these records itself until the client cancels the cursor view on the data.
This caching has a number of limitations:
- SQL Server has no way of controlling or minimizing the locks held on the database, and so any locks held will be held for all data pages in the cursor, not just the affected data pages. This is because SQL Server is basically unaware that anything other than a select activity is occurring on the data.
- Client-side resources can be consumed very quickly if there are large sets of data.
- The caching is inefficient when processing large amounts of data because all the data is being sent across the network unnecessarily.
Clearly, Microsoft's client cursors were just a stop-gap measure until the real work of server cursors could be completed. 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 a SQL Server 6.5 application.
Server cursors generally have five states when being used, as shown in Table 15.1.
| State | Explanation |
|---|---|
| DECLARE | At this point, SQL Server validates that the query that's going to be used to populate the cursor is valid. SQL Server creates a structure in shared memory 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 | The data is now being returned from the cursor, and any activity that's required can be performed. |
| CLOSE | SQL Server closes the previously opened cursor and releases any locks that it may have held as a result of opening it. |
| DEALLOCATE | SQL Server releases the shared memory used by the DECLARE statement, no longer permitting another process from performing an OPEN on it. |
Using SQL Server Cursors
Using a cursor in SQL Server involves following the states previously described. This section explains the steps required to use a cursor effectively in your applications.You first must declare the cursor. Once a cursor has been declared it can be opened and fetched from. During the fetch phase or state of a cursor any number of operations may be performed on the currently active row in the cursor. When you have finished working with a cursor, you need to close and deallocate it so that SQL Server does not waste resources managing it any further.
Declaring a Cursor
Declaring a cursor is very similar to requesting data using a standard Select statement. Note that the Select statement used 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]}]
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. You must use named columns in the data tables that correspond one to one with the variables used in the FETCH clause.
The options for the Transact-SQL command DECLARE CURSOR are as follows:
—The name of the cursor must comply with the standard object identifier rules of the database.
Be careful when using the INSENSITIVE keyword in defining a cursor. Applications that use this keyword may run into problems of inaccurate data if the application has high transaction loads on the underlying table or tables that the cursor is being opened on. If the application being written is time driven, however (for example, "Tell me what our balance sheet position is as of right now"), INSENSITIVE cursors are a requirement.
Listing 15.2 shows a basic cursor that's being declared to fetch the data from a single table (Employee) in the Pubs database.
Listing 15.2 15_2.SQL—Cursor Being Declared to Retrieve Information From 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 15.2, provides no greater application flexibility than a simple SELECT on the data. If, however, the application required absolute row positioning, as shown in Listing 15.3, it's possible to add the SCROLL keyword to the Declare statement that makes the cursor very different in comparison to a table SELECT.
Listing 15.3 15_3.SQL—Scrollable Cursor Being 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 a cursor is declared, SQL Server reserves handles for its use. To use a cursor and fetch data from it, you must open the cursor. To open a cursor, use the following syntax:
Open Cursor_Name
In the preceding examples, the code required to open the cursor would have been either
Open Cur_Empl
or
Open Cur_Empl_Scrollable
When a cursor is opened, SQL Server resolves any unknown variables with their current state. If a cursor was declared with a variable in the Where clause and then opened, for example, the value used to resolve the query would be the value that the variable held at the time the cursor was opened. For example,
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
...
You can determine how many rows were found by the cursor by evaluating @@Cursor_Rows. If the number of rows is negative, the cursor hasn't yet determined the total number of rows, as would be the case where it may still be 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 has been closed and/or deallocated.
Fetching a Cursor
After a cursor is in an opened state, you can fetch data from it. Unless a cursor is declared with the SCROLL keyword, the only kind of fetching permissible is serially/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:
- NEXT—The NEXT keyword, implicit in normal fetching operations, implies that the next available row be returned.
- PRIOR—If the cursor was defined with SCROLL, this keyword will return the prior record. It's unusual for stored procedure-based applications to take advantage of this keyword unless they're responding to some kind of error condition by logically rolling back the previous row update.
- FIRST—This keyword fetches the first record of the result set found by opening the cursor.
- LAST—This keyword fetches the last record of the result set found by opening the cursor.
- ABSOLUTE n—This keyword will return the nth row in the result set. If you specify a positive number, the rows are counted from the top of the data set. If you provide a negative value for n, the number of rows will be counted from the bottom of the data set.
- RELATIVE n—This keyword will return the nth row in the result set relative to the current record that has most recently been fetched. If the number is negative, the row will be counted backward from the current row.
In SQL Server 6.5, Microsoft has enhanced the syntax of the FETCH ABSOLUTE and FETCH RELATIVE statements by allowing @ variables to be substituted for n. If a variable is used, then it may only be of the types int, smallint, or tinyint. - FROM—This is an an unnecessary keyword provided to make the code slightly more readable. It indicates that the next word will be the cursor that's being fetched from.
- INTO—The INTO keyword is provided for stored procedure use so that the data returned from the cursor may be held in temporary variables for evaluation and/or other use. The data types of the variables in the INTO clause must match exactly the datatypes of the returned columns of the cursor; otherwise, errors will be generated.
Closing a Cursor
Closing a cursor releases any resources and/or locks that SQL Server may 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
Deallocating a cursor completely removes any data structures that SQL Server was holding open for a given cursor. Unlike closing a cursor, after a cursor is deallocated, it no longer can be opened.To deallocate a cursor, use the following syntax:
DEALLOCATE cursor_name
Example of Using Cursors
In the previous sections we have seen all the separate elements that are used to work with cursors in SQL Server. However, we haven't seen how all the elements are put together—that's what this section is for.In Listing 15.4, we will examine the use of cursors and see them in action. Refer to the comments in the script (placed between /* ... */) to get a good understanding of what the cursors are doing.
Listing 15.4 15_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 required
resource structures needed 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: Cleanup - 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 totalling 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
The resulting output from running this listing is shown below so that you can see what would have happened if you ran it:
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
Sorting variables in large procedures alphabetically will make it much easier to find them. In addition, you can sort the variables by datatype as well, so that it is even easier to find them—this will happen automatically if you prefix variables with a datatype indicator such as: s for strings, n for numbers and dt for date/times.
Using Nested Cursors
You can have multiple layers of cursors in a stored procedure that you can use to provide flexible result-set processing. An example of this might be when you're opening a cursor, as shown earlier in the Cur_Empl example. In addition to the cursor 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 15.5 shows you what's possible with nested cursors.
Listing 15.5 15_5.SQL—Using Cursors in a Nested Fashion
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 /* only fetch 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
SQL Server treats object names case-insensitively, regardless of the sort order defined for the server. So take advantage of this and make your code easy to read by using upper- and lowercase emphasis when possible.
This was quite a complex example of the kinds of things you can do with multiple nested cursors. However, it is important to remember that using cursors is really not that difficult provided that 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're accessible 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 won't be able to distinguish the data from that returned by a normal select statement. Typically, the tool will have 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 cursors 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 and whatever programming language in which you choose to do your development should be able to support anything that SQL Server can return. For a more detailed discussion on several client/server programming tools, refer to Chapter 22, "Accessing SQL Server Databases Through Front-End Products"
From Here...
In this chapter you learned about SQL Server's new server-based cursors and how they can be used to provide much more processing power to your applications without the need to return to the client for help. After I spoke with the performance-tuning group at Sybase, whose understanding of SQL Server's cursors is very intimate, it became clear that 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? No networking is involved. Unless the type of work you're doing can't be modeled in stored procedures, as may be the case with arrays, then all batch operations should be moved to cursor-based procedures running on the server.
From here, I suggest that you explore the following chapters to implement what you've learned:
- Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements"explains how to start embedding cursor processing in your stored procedures.
- Chapter 22, "Accessing SQL Server Databases Through Front-End Products" provides a full explanation of how your front-end tool of choice interacts with the database when using cursors.
