Lesson 2: Using Cursors | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

Cursors offer the ability to navigate forward and backward through a result set one row at a time in order to view or process data. Think of a cursor as a pointer to a current position or a specific row within a result set.

After this lesson, you will be able to:

Describe when to use Transact-SQL cursors

Determine which cursor type and characteristics are appropriate for a cursor

Create and use a cursor to retrieve data

Estimated lesson time: 60 minutes

Cursors extend standard result set processing by

Allowing positioning at a specific row in the result set

Retrieving and modifying one row or block of rows from the current position in the result set

Supporting different levels of sensitivity to changes to the data underlying the result set

Requesting Cursors

Microsoft SQL Server version 7.0 supports two methods for requesting a cursor:

Transact-SQL

Application programming interface (API) cursor functions

This lesson discusses only Transact-SQL cursors in detail. If you require details for API cursor functions, check the SQL Server Books Online.

SQL Server supports the cursor functionality of the following database APIs and object interfaces:

ADO (ActiveX Data Object)

OLE DB

ODBC (Open Database Connectivity)

DB-Library

All cursors require temporary resources to cache data. These resources can be in the form of RAM, a paging file (such as the virtual memory feature of Microsoft Windows), temporary files, or databases. You should not create excessively large cursors or use cursors unnecessarily because they temporarily consume server resources that may degrade the overall performance of SQL Server and user applications.

Cursors may be cached on the client or the server. The placement is driven by the following factors:

When you request a client cursor, SQL Server sends the entire result set across the network to the client.

When you request a server cursor, SQL Server uses its own server resources to manage temporary objects. Only rows that are selected within the cursor are returned to the client over the network.

API cursors may be cached on the client or the server. Transact-SQL cursors are always cached on the server.

Transact-SQL Cursors

This lesson discusses the uses of Transact-SQL cursors, how Transact-SQL cursors work, the types and characteristics of Transact-SQL cursors, and the syntax for using Transact-SQL cursors. It also describes how to work with data from cursors and how to gather information about cursors.

Uses of Transact-SQL Cursors

Use Transact-SQL cursors for complex, row-oriented functionality.

Building a Command String for Execution

You can use Transact-SQL cursors to build command strings that include parameters such as database object names or data from user tables. You might use a cursor in a stored procedure to execute a Transact-SQL statement (such as UPDATE STATISTICS) on many related objects in a database.

Increasing Script Readability

Using a cursor rather than a nested SELECT statement can sometimes improve the readability of a stored procedure. If this is the case and if the performance of the two queries is comparable, using a Transact-SQL cursor is appropriate.

Performing Multiple Unrelated Manipulations with Data

You can pass local cursors between stored procedures when you want to generate a result set once and perform unrelated manipulations on the result set. For example, you can use a cursor to offer users multiple ways to view the data in a result set.

Compensating for Database and Application Limitations

You also can use cursors to compensate for database and application limitations. An example of this is the sp_helptext system-stored procedure. This system-stored procedure uses a Transact-SQL cursor to break down the text of a database object into multiple rows. This feature was developed because previous versions of SQL Server had a 255-character limit on the char data type.

How Transact-SQL Cursors Work

Follow the steps illustrated in Figure E.2 when you work with Transact-SQL cursors.

Declare the cursor. This step includes using the SELECT statement that generates a result set and defines the characteristics of the cursor, such as whether the rows in the cursor can be updated.

Execute the OPEN statement to generate the result set and populate the cursor.

Retrieve rows from the cursor result set. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches is called scrolling.

Use values from or perform operations on the row at the current position of the cursor.

Close and deallocate the cursor.

click to view at full size

Figure E.2 How Transact-SQL cursors work

Example

This example, used in Figure E.2, creates a cursor based on a query that returns the names of the tables in the Northwind database from the sysobjects system table. After the cursor is opened, the script navigates through the cursor result set, executing the sp_spaceused system-stored procedure for each table. When the cursor processing is complete, the cursor is closed and deallocated.

 USE Northwind  DECLARE tbl_space CURSOR     FOR     SELECT name FROM sysobjects      WHERE type = 'U' ORDER BY name  OPEN tbl_space  DECLARE @tbl_name sysname  FETCH NEXT FROM tbl_space INTO @tbl_name  WHILE (@@FETCH_STATUS = 0)      BEGIN           EXEC sp_spaceused @tbl_name          FETCH NEXT FROM tbl_space INTO @tbl_name     END  CLOSE tbl_space  DEALLOCATE tbl_space

Results

 name                 rows        reserved           data       -------------------- ----------- ------------------ ------   Categories           8           184 KB             112 KB   name                 rows        reserved           data      -------------------- ----------- ------------------ ------   CustomerCustomerDemo 0           32 KB              8 KB       name                 rows        reserved           data      -------------------- ----------- ------------------ ------   Territories          53          32 KB              8 KB

Types of Transact-SQL Cursors

SQL Server supports four types of cursors: forward-only, static, dynamic, and keyset-driven. The types of cursors vary in their ability to detect changes to the result set and in the resources that they use, such as memory and space in the tempdb database.

If you have a cursor open, and the data in the underlying table on which the cursor is based is changed by other connections, different cursor types may or may not reflect those changes. The following table shows how the cursor type that you choose determines whether your cursor result set reflects changes that are made to the membership, order, or values of the data in the underlying table:

Cursor Type	Membership	Order	Values
Forward-only	Dynamic	Dynamic	Dynamic
Static	Fixed	Fixed	Fixed
Dynamic	Dynamic	Dynamic	Dynamic
Keyset-driven	Fixed	Fixed	Dynamic

Forward-Only Cursors

A forward-only cursor only supports fetching rows serially from the first to the last row of the cursor. It does not retrieve rows from the database until the rows are fetched from the cursor; therefore, temporary storage resources are not an issue. The effects of all INSERT, UPDATE, and DELETE statements that any other connection makes before a row is fetched are visible when the row is fetched.

Static Cursors

A static cursor fixes a result set when the cursor is opened and the result set is always read-only. Thus, it is not possible to update the underlying tables of a static cursor through the cursor. The result set is stored in tempdb when the static cursor is opened. Changes made by other connections after the cursor is opened are never reflected by the cursor. Static cursors are also called insensitive or snapshot cursors.

Dynamic Cursors

Dynamic cursors are the opposite of static cursors. A dynamic cursor reflects all changes that are made to the data values, order, and membership of rows in the result set when you scroll through a cursor result set. The effects of all UPDATE, INSERT, and DELETE statements that any user makes are visible as rows are fetched through the cursor. You can fetch rows randomly from anywhere in the cursor. Dynamic cursors are also called sensitive cursors.

Keyset-Driven Cursors

The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a unique set of identifiers (keys), known as a keyset. The keys are built from a set of columns that uniquely identify a set of rows in the result set. The keyset is the set of key values from all the rows that qualified for the SELECT statement when the cursor was opened, and the membership and order of rows is never updated.

Inserts and updates that the user makes through the cursor are visible, as are changes that other connections make to data values in nonkeyset columns. The keyset for a keyset-driven cursor is stored in tempdb when the cursor is opened.

NOTE
Cursors with fixed characteristics of membership, order, and values perform faster than cursors with dynamic characteristics, although they may be slower to open initially.

Consider the following facts when you determine which type of cursor to use:

Dynamic cursors open faster than static or keyset-driven cursors.

In joins, keyset-driven and static cursors can be faster than dynamic cursors.

Static and keyset-driven cursors increase the use of the tempdb database. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb. For this reason, if a large number of users each open a static or keyset-driven cursor, tempdb may run out of space.

Transact-SQL Cursor Characteristics

You can define the behavior and locking characteristics of cursors. Transact-SQL cursors and ODBC API cursors support specifying scrollability and sensitivity. Not all API cursors support specifying behaviors. All cursors support varying levels of locking.

Scrollability

Scrollability defines the fetch options that the cursor supports. Cursors can be scrollable or forward-only. A scrollable cursor supports all fetch options; a forward-only cursor supports only the fetch next option.

Sensitivity

Sensitivity defines whether updates that are made against the base rows are visible through the cursor. Sensitivity also defines whether you can make updates through the cursor. If you make an update to the current row of a cursor, the actual update is made to the underlying table; this is called a positioned update.

A sensitive cursor reflects data modifications that anyone makes. Positioned updates can be made through the cursor, except when a read-only-sensitive cursor is used. An insensitive cursor is read-only and does not support updates.

Locking

Because SQL Server must acquire an exclusive lock before it can update a row, updates that are made through a cursor can be blocked by other connections that hold a shared lock on a row. The transaction isolation level of a cursor allows a programmer to determine the full locking behavior of a specific cursor environment.

When any row in a cursor is updated, SQL Server locks it with an exclusive lock. Locks are held for varying lengths of time, depending on the situation:

If the update is performed within a transaction, the exclusive lock is held until the transaction is terminated.

If the update is performed outside of a transaction, the update is committed automatically and, when it is complete, the exclusive lock is freed.

Transact-SQL Cursor Syntax

You use five statements when you work with Transact-SQL cursors. The following table summarizes these five statements:

Statement	Description
DECLARE CURSOR	Defines cursor structure and allocates resources
OPEN	Populates a declared cursor with a result set
FETCH	Navigates within a cursor result set
CLOSE	Releases the current result set and frees any cursor locks held on the rows on which the cursor is positioned
DEALLOCATE	Removes the cursor definition and deallocates resources

DECLARE CURSOR

The DECLARE CURSOR statement defines the characteristics of the cursor, the query that the cursor uses, and any variables that the cursor uses.

There are two forms of the DECLARE CURSOR syntax. The first form of the DECLARE CURSOR statement uses the ANSI SQL-92 syntax (preferred). The second form uses the extended Transact-SQL cursor syntax.

ANSI SQL-92 Syntax

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

Transact-SQL Syntax

Consider the following facts when you declare cursors:

The select_statement is a standard SELECT statement. Certain keywords, such as DISTINCT and UNION, force the cursor type to static.

LOCAL or GLOBAL specifies the scope of the cursor. The cursor can be local to a stored procedure, trigger, or Transact-SQL script, in which case it can be used only by that code and is removed when the code is finished executing. If the cursor is global, it may be used by subsequent stored procedures, triggers, or Transact-SQL scripts executed on the connection and is not removed until the connection is closed or until the cursor is closed and deallocated. The default is global.

The current user must have SELECT permission on the table and the columns that are used in the cursor.

OPEN

The OPEN statement opens and populates the cursor by executing the SELECT statement. If the cursor is declared with the INSENSITIVE or STATIC options, OPEN creates a temporary table to hold the rows of the cursor. If the cursor is declared with the KEYSET option, the OPEN statement creates a temporary table to hold the keyset only.

Syntax

OPEN{ {[GLOBAL] cursor_name} | @cursor_variable_name}

NOTE
The @cursor_variable_name option allows you to reference a cursor that has been associated with a cursor variable. This option is generally used in stored procedures.

FETCH

The FETCH statement retrieves a specific row from the cursor.

Syntax

FETCH
[[NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}]
FROM ]
{{[GLOBAL] cursor_name} | @cursor_variable_name}
[INTO @variable_name[,...n]]

Consider the following facts when you use the FETCH statement:

The type of FETCH option that is supported within a cursor depends on the type of declared cursor.

The @@FETCH_STATUS function is updated at every execution of the FETCH statement. Use @@FETCH_STATUS before you attempt to operate on the data to determine whether a fetch was a success or a failure.

CLOSE

The CLOSE statement releases the current result set. It releases any locks that are held on the row on which the cursor is positioned but leaves the data structures accessible for reopening. Modifications and fetches are not allowed until the cursor is reopened. Closed cursors may be reopened.

Syntax

CLOSE {{[GLOBAL] cursor_name} | @cursor_variable_name}

DEALLOCATE

The DEALLOCATE statement removes the association between a cursor and a cursor name or cursor variable that references the cursor. If the cursor name or cursor variable is the last reference to the cursor, the data structures used by the cursor are released.

Syntax

DEALLOCATE {{[GLOBAL] cursor_name} | @cursor_variable_name}

Working with Data from Cursors

In addition to navigating through data in cursors, you can modify data through a cursor or share the data.

Modifying Data through Cursors

You can modify data through local and global Transact-SQL cursors. You must declare the cursor as updateable. In an updateable cursor, you can use UPDATE or DELETE statements with the WHERE CURRENT OF cursor_name clause to modify the current row. In the following example, a cursor called ShipCurs has been created on the Shippers table in the Northwind database, and the CompanyName column of the shipper in the current row is updated:

 USE Northwind  UPDATE Shippers   SET CompanyName = N'Speedy Express, Inc.'  WHERE CURRENT OF ShipCurs

Passing Local Cursors between Stored Procedures

Stored procedures that declare and open local cursors can pass the cursors out for use by the calling stored procedure, trigger, or batch. Passing a cursor is done by declaring a variable, with the cursor varying data type as an output parameter. Variables that are used to pass cursors are called cursor variables.

Consider the following facts about cursor variables:

Cursor variables can be used only as output parameters; they cannot be used as input parameters.

Local variables can also be declared with the cursor data type to hold a reference to a local cursor.

Pass local cursors between stored procedures when you want to generate a result set once and perform unrelated manipulations on the result set. For example, you could pass a local cursor to allow users to choose from a list of manipulations to perform on the cursor result set.

Getting Cursor Information

Various system-stored procedures and functions provide information about cursors.

System-Stored Procedures

When a cursor has been declared, you can use the following system-stored procedures to determine the characteristics of the cursor. These system-stored procedures do not return a standard result set. They all report their output as an output cursor, so you have to declare a cursor variable and then fetch the rows from the cursor in order to use them.

System-Stored Procedure	Description
sp_cursor_list	Returns a list of cursors that are currently opened by the connection and the attributes of the cursors
sp_describe_cursor	Describes the attributes of a cursor, such as whether it is forward-only or scrolling
sp_describe_cursor_columns	Describes the attributes of the columns in the cursor result set
sp_describe_cursor_ tables	Describes the base tables that the cursor accesses

Functions

Many functions return information about the status of a cursor. The following table describes three of the more commonly used functions:

Global Variable	Description
@@FETCH_ STATUS	Returns the status of the last cursor FETCH statement that was issued against any cursor that is currently opened on the connection
@@CURSOR_ ROWS	Returns the number of qualifying rows in the last cursor that is opened on the connection
CURSOR_STATUS	After calling a stored procedure that returns a cursor as an output parameter, this function allows you to determine the status of the cursor that was returned

Exercise 1: Using a Cursor to Find the Median Unit Price in the Products Table

In this exercise, you will execute a script that creates a cursor to find the median unit price of all products in the Northwind database. If the cursor holds an odd number of products, the median is the middle row. If the cursor holds an even number of products, the median is the average of the two middle rows. A scrollable cursor is used so that the middle row(s) can be retrieved directly using an absolute fetch.

To execute a script that uses a cursor to find the median unit price in the Products table

Open SQL Server Query Analyzer.

Open the C:\SQLDW\Exercise\AppE\cursor.SQL script file. Review and then execute the script.

Lesson Summary

Cursors make it possible to perform row-based operations on SQL result sets. Transact-SQL provides syntax for creating cursors on SQL Server; you can also use API-based client and server cursors with SQL Server 7.0. Various types of cursors are available. The types vary in their scrollability, sensitivity, and locking characteristics.

When you declare and open a Transact-SQL cursor, a temporary structure is created in the tempdb database to cache some or all the cursor data. The FETCH statement is then used to position the row pointer in the cursor and retrieve individual rows.