Intro

Most SQL statements generate rows of data called a result set (aka comsor, a COMplete Set Of Rows).

Usually a client wants this result set so he orders and gets a default result set (aka firehose "cursor") is used. The process for default result sets is like Joe calling Uno's for an order for 5-500 pizzas. The restaurant pumps the pizzas out as fast as they can and there is no additional communication between Joe and Uno's. Here is the basic process for a default record set:

  1. SQL Server gets a network packet from a client to execute some SQL.
  2. SQL Server executes the SQL.
  3. SQL Server immediately starts stuffing rows from the result set being generated into network packets and sends them to the client.
  4. The client receives the network packets and caches them in a network buffer. The client app uses the ODBC driver, OLE DB driver, or DB-Library DLL to get the rows from the buffer one at a time.

Default result sets are extremely efficient. Default result sets can be used with practically all SQL statements and can even execute batches or stored procedures that generate multiple result sets.

Sometimes though, you want to do something different: like access a fraction of the entire result set or use the result set by other code on the server. In that case you may want a cursor. A cursor (aka CURrent Set Of Records) is a mechanism for accessing a row or block of rows from a result set.

The main option for a cursor is whether the data is on the client or the server.

  • Client cursors are good for spreading the workload across many workstations.
  • Server cursors are good for lightweight clients who can't afford to cache a large result set. Server cursors do not support multiple result sets or these SQL keywords: COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.

Once the client or server option is selected, the cursor is further specified by Cursor Type or Cursor Behavior, both of which basically do the same thing.

  • Cursor Type. This is used by T-SQL, ODBC, ADO, and DB Library cursors to specify the way a cursor is implemented.
    • Forward-Only. Rows are fetched serially. Once a row if fetched, changes to the row are not detected. Also if a row has not been fetched yet, then change can occur until the row is fetched.
    • Static cursors (aka snapshot or insensitive) do not detect changes to rows, or new or deleted rows. The original result set is actually stored in a temp table in the SQL Server database called tempdb for the purpose of fetching. Good for displaying data that does not need to be modified. Only available with server cursors.
    • Keyset cursors detect changes to rows, but not new or deleted rows. A key (unique identifier) is made for each row in the result set and the whole keyset is stored in a temp table in tembdb. Only available with server cursors.
    • Dynamic cursors detect changes to rows, as well as new or deleted rows. Only available with server cursors.
  • Cursor Behavior. This is used by either T-SQL or ODBC API cursors to specify two behaviors: scrolling and sensitivity.
    • Scrolling Behavior . The default is non-scrolling, i.e. forward-only. Scrollable cursors can move forward and backward and can also set bookmarks to desirable records. Only available with server cursors.
    • Sensitivity Behavior . Sensitive cursors detect modifications to the data that made the cursor. Sensitive cursor can also implement update to the database.

With these definitions in hand, it is possible to say that a fire hose cursor is, in one sense, a client-side cursor that is of Cursor Type Forward-Only and has a Cursor Behavior of non-scrolling and insensitive.

Implementing Cursors

The syntax for specifying a cursor is dependent on the method for making the cursor. SQL Server supports two possible methods for making cursors: via DECLARE CURSOR or via a DB API. Applications should choose one or the other but not both.

  • The SQL-92 compliant syntax which utilizes DECLARE CURSOR. These are server-side cursors used mainly in Transact-SQL scripts, stored procedures, and triggers. Here is the basic process:
    1. DECLARE T-SQL variables of the appropriate data type for each result set column.
    2. DECLARE a T-SQL variable of the data type cursor and associate it with a SELECT statement.
    3. Populate the cursor variable by using the OPEN statement to execute the SELECT statement.
    4. Fetch individual rows by using FETCH INTO. Pass column values into the corresponding variables made in step 1.
    5. Free resources by using CLOSE and DEALLOCATE.
  • The cursor functionality provided by various DB APIs, including the following: ADO, OLE DB, ODBC, and DB-Library. These can be server or client side. ADO only allows four cursors: Keyset (1), Static (3), Dynamic (2), and Forward-Only (0, the default, i.e. the fire hose cursor). Here is the basic process:
    1. Open a connection.
    2. Define the cursor.
    3. Execute SQL statements.
    4. Use API functions or methods to fetch the rows in the result sets.

Please note that a cursor can be set to close automatically (or not) when a transaction is committed with this T-SQL syntax:

SET CURSOR_CLOSE_ON_COMMIT { ON | OFF }

There are two syntaxes to DECLARE a T-SQL variable of data type cursor, one is SQL-92 compliant, the other is a T-SQL version. The two cannot be mixed.:

DECLARE CursorName [INSENSITIVE] [SCROLL] CURSOR
FOR SelectStatement
[FOR {READ ONLY | UPDATE [OF ColumnName [, ...]]}]
DECLARE CursorName CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR SelectStatement
[FOR UPDATE [OF ColumnName [, ...]]]

There are two ways to use a variable of data type cursor in T-SQL.

  • Use DECLARE @LocalVariable, DECLARE CURSOR and SET. EG:
    DECLARE @MyVariable CURSOR
    DECLARE MyCursor CURSOR
    FOR SELECT LastName FROM Northwind.dbo.Employees
    SET @MyVariable = MyCursor
    
  • Use DECLARE @LocalVariable and SET. EG:
    DECLARE @MyVariable CURSOR
    SET @MyVariable = CURSOR
    FOR SELECT LastName FROM Northwind.dbo.Employees
    

Here is they syntax to FETCH from a T-SQL variable of data type cursor.

FETCH
[ [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
    FROM
]
{{[GLOBAL] CursorName} | @CursorVariableName}
[INTO @VariableName [, ...]] 

Here are how the different DB APIs fetch from cursors:

  • OLE DB uses methods such as IRowset::GetNextRows, IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsAtBookmark, and IRowsetScroll::GetRowsAtRatio .
  • ODBC uses the SQLFetch function (which is the same as a FETCH NEXT for one row), or the SQLFetchScroll function. SQLFetchScroll supports block cursors and all the fetch options (first, last, next, prior, absolute, relative).
  • ADO uses the Move, MoveFirst, MoveLast, MoveNext, and MovePrevious method of the Recordset object to acquire a position in a cursor. The GetRows recordset method is then used to retrieve one or more rows at that position. GetRows can also be called directly with the Start parameter set to the number of the row to fetch.
  • DB-Library uses the dbcursorfetch and dbcursorfetchex functions.

Examples

Example 1

Here is a basic T-SQL implementation of a firehose cursor.

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE 'D%'
ORDER BY au_lname

OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM authors_cursor
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Example 2

Here is a cursor nested inside of another to make a report. This example is straight out of the MSDN Library.

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " +
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0
      PRINT "         <<No Books>>"

   WHILE @@FETCH_STATUS = 0
   BEGIN

      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title

   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor

   -- Get the next author.
   FETCH NEXT FROM authors_cursor
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------

----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?

----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear


GeorgeHernandez.comSome rights reserved