How cursors work

http://searchsqlserver.techtarget.com/feature/Part-1-How-cursors-work

To visualize how a cursor works, think of the apparatus used by a skyscraper window washer to travel up and down the skyscraper, stopping at each floor to wash each window. For most cursor types, key data is brought into memory and the cursor navigates this key data on a row-by-row basis; similar to the window washer going floor by floor.

A cursor requires two operations. The placement operation moves the cursor to a row in the results set. The retrieve statement returns the data underlying that row, called a fetch. Set operations accomplish this with a single statement:

• select * from TableName where pk=1

Keeping in mind the window-washer analogy, let's walk through the steps you would take in using a cursor.

Two sets of syntaxes are supported by cursors: SQL-92 and T-SQL Extended Syntax. For the most part I will look at the T-SQL Extended Syntax and reference the SQL-92 syntax for comparative purposes. There are no new cursor features in SQL Server 2005.

First you create the cursor using a declare statement, which involves setting the cursor options and specifying the results set.

Cursor options

There are four sets of cursor options:

STATIC
The STATIC cursor copies the data in the results set into tempdb and DML that occurs in the underlying results fails to reflect in the cursor's data. Subsequent fetch statements are made on the results set in tempdb. This is perfect when the data underlying your cursor is static or your cursor has no real-time requirements. For example, most cursors Microsoft uses are declared as static as the operation being carried out on the data needs to be done on point-in-time requirements. In other words, it does not need to know about new rows -- the data is static.

KEYSET
The KEYSET cursor is implemented by copying primary key data into tempdb. As the cursor moves through the result set, the cursor will see modifications to the underlying data but it will not see new rows. If data is fetched from a row that no longer exists, nulls will be returned and the @@FETCH_STATUS variable will have a value of -2. The order of the cursor data is also maintained. The KEYSET cursor is the default cursor type. Fetch statements are made on the underlying base tables based on the keyset data cached in tempdb. These cursors take more time to open than DYNAMIC cursors, but they also have fewer resource requirements.

DYNAMIC
The DYNAMIC cursor is similar to the KEYSET cursor in that the cursor can see data modifications in the underlying base tables, but it can also see newly inserted and deleted rows. It does not preserve order, which can lead to the Halloween problem as illustrated in script 4. Fetch statements are made on the underlying base tables, based on the key data cached in tempdb, but the key data is refreshed with each modification to key data on the base tables. It is the most expensive cursor type to implement.

FAST_FORWARD
A FAST_FORWARD cursor provides optimal performance but only supports the NEXT argument, which only fetches the next row. Other cursor types will be able to fetch the next row, the prior row (using the PRIOR command), the first row (using the FIRST argument), the last row using the LAST argument, the nth row (using the ABSOLUTE arguments), or leap ahead n rows from the current cursor location (using the RELATIVE argument).

The above cursor options control the following:

Scope or visibility
Is the cursor only visible within a batch (local) or beyond the batch (global)? Cursors are only visible within a connection.

Scrollability
Can the fetch statement fetch only the next row, fetch in any direction and/or by a specific number of rows? The advantage of a forward-only cursor is that it performs faster than a cursor type that can move in any direction. The two options are FORWARD_ONLY and SCROLL (any number and in any direction).

Membership
Which rows are members of your cursor? Can the cursor see changes happening in the underlying results set, and can it see newly inserted/deleted rows?

Updatability
Can you update or delete the rows in the underlying results set? To update the tables underlying your cursor, you must have the following:

1. A primary key on the base tables underlying your cursor to update them. Otherwise you will get the message:

Server: Msg 16929, Level 16, State 1, Line 1
The cursor is READ ONLY.
The statement has been terminated.

2. A cursor defined as KEYSET, DYNAMIC or FAST_FORWARD.

3. The WHERE CURRENT syntax to update or delete a row. Please refer to this script for an illustration of cursor updatability functions.

You retrieve rows from the cursor using fetch statements. You should always check the value of the @@Fetch_Status variable to ensure that it has a value of 0. The @@Fetch_Status variable can have three values:

• 0 - row successfully returned
  -1- fetch statement has read beyond the number of rows in the cursor 
  -2 - row no longer exists in your results set

The fetch statements are analogous to our window washers moving down the sides of the sky scraper. With the fetch statement, the logical operations are position and then retrieve; twice as many operations as a set statement (i.e., with a set statement it's INSERT, UPDATE or DELETE).

Finally, clean up after your cursor using the close MyCursorName statement and then deallocate its resources using the deallocation MyCursorName statement. Note that a quick way to return to the beginning of a FAST_FORWARD cursor is to close and reopen it.