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.
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.
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.
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.
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. |
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 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.
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.
Declare Cur_Empl_Scrollable SCROLL Cursor For Select EMP_ID, LNAME, JOB_ID, PUB_ID From EMPLOYEE Order By EMP_ID Go
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.
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.
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 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.
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
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:
/* 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.
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.
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.
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."
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.
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:
© Copyright, Macmillan Computer Publishing. All rights reserved.