Week 2

Day 14
Cursors

Cursors enable you to take a set of data and deal with it on a row-by-row basis, rather than as a set of rows. Today, I talk about the following topics relating to cursors:

•  What is a cursor

•  Client cursors and server cursors

•  Limitations of cursors

•  Row processing (cursors) vs. set processing (SQL)

•  Cursors and performance

•  Cursors and locking

What Is a Cursor?

A cursor is a programming construct that allows each row in a table to be manipulated separately from any other rows. This can be useful when the rows in the table cannot be manipulated easily with set-oriented commands.

Regular SQL DML commands (insert, update, and delete) operate on sets. Examine the following query:

update titles
set  price = price  *  2

(18 row(s) affected)

The set on which this query operates is the whole table. The server has received instructions that mean: “For every row in the table, double the price.”

A cursor accepts commands for processing each row rather than a set of rows. For each row in the titles table, for instance, a set of operations can be carried out. Based on the values in the row, different operations can be performed on the same row.

One benefit to using a cursor is that multiple operations can be performed on a single row before moving to another. You may use a cursor to read or to modify rows.

Creating a Cursor

There are five steps, and five different commands, that you must use to create, use, and destroy cursors. These five steps are in stark contrast to the single step required to issue standard SQL commands. The steps are

1.  Declare the cursor. Declaration must occur in its own batch. This allocates memory and data structures on the SQL server.

2.  Open the cursor.

3.  Fetch rows. This step is where all the work takes place. Typically, a cursor program will fetch a row, use the row in one of several various ways, and fetch another. Starting with MS SQL Server 6.0, cursors can move forward or backward. These are called scrollable cursors. Older versions of the server did not support cursors. (There were client-side cursors back in the old days, though. More on that in a minute.)

4.  After all rows have been fetched, close the cursor. You may open a cursor after closing it, to reopen the cursor set from the first row.

5.  Deallocate the cursor. This frees the server memory used by the cursor.

A Simple Cursor Example

I will show you a very simple cursor example, which reads rows from the titles table. If the book is a business book, it adds 25 percent to the price. Otherwise, it simply prints the normal price.

Step 1: Declare the Cursor

This step must appear in its own batch.

declare titles_curs cursor
for
select   title, type, price
from     titles
order by type,  title
for read only

(0 row(s) affected)

This is the same DECLARE keyword you used on Day 12 to declare local variables. You may declare variables or declare a cursor with a single DECLARE statement, but not both. To do both, use two DECLARES.

You must provide a name for the cursor in the DECLARE. I always reverse the name and CURSOR keyword—I don’t really use cursors often enough for the syntax to be burned into my head.

Following the CURSOR keyword, you define the set of rows on which the cursor will act. This is defined as a regular SELECT statement, whether you intend to use the cursor to modify rows or not. This cursor is defined as a read-only cursor. If you wanted to alter rows, you would still use a SELECT statement to indicate those rows, but you would declare the cursor for update instead of for read only. If you do not specify a cursor type, it defaults to read-only.

There are two additional cursor options that do not appear here, INSENSITIVE and SCROLL. Either or both of these two keywords may be placed after the cursor name to activate the option.

The SCROLL option activates all scrolling options. Otherwise, only next fetches are available. Scrolling options are discussed in the fetch step (Step 3). Forward-only cursors use less memory and are more efficient and faster, especially on large tables.

Here is the syntax of the DECLARE statement when used to create a cursor:

declare <cursor name> [insensitive] [scroll] cursor
for <select statement>
[for <read only | update [of <column list>]>]

You may select columns for a cursor, but allow updates to only some of those columns by providing an update column list, as shown in line three of the preceding syntax statement.

---

Note:  Updating with cursors in 6.0
In v6.0, the server tracked the current cursor row by using the most efficient unique index on the table. Without a unique index, only read-only cursors were supported. In 6.5, a unique index is not required to create a cursor.

---

Step 2: Open Cursor

After declaring the cursor, be sure to execute the declaration. The DECLARE statement must be made in its own batch. Once this has been done, you are ready to open the cursor. This is a very simple step.

open cursor title_curs