INF: Overview of SQL Server, ODBC, and DB-Library Cursors

ID: Q156489

The information in this article applies to:

Microsoft SQL Server version 6.0

SUMMARY

This article outlines the cursor models offered by SQL Server 6.0 server cursors, the DB-Library Cursor Library, and Open Database Connectivity (ODBC). Users who want a more in-depth discussion of cursors can read the section on the ODBC Cursor Model in "Inside ODBC" by Kyle Geiger, available from Microsoft Press in most bookstores.

MORE INFORMATION

All operations in a relational database operate on a complete set of rows. For example, an UPDATE command works on the set of all rows that satisfy the conditions in the UPDATE's where clause. However, there are times when an application's work can be more easily implemented by performing a series of operations based on subsets of rows in a result set, or by working through a result set one row at a time. This is especially true for interactive client-server applications that present result set rows to a user who wants to scroll up and down the result set in blocks of rows at a time.

Cursors offer applications the ability to process through a result set one row, or one block of rows, at a time. Think of a cursor as a pointer to a current position, or a specific row, within the result set of a single SELECT command. The following is a simple example of an application using a cursor to retrieve all the rows that result from a SELECT:


   DECLARE cursor_name CURSOR FOR select * from some_table
   OPEN cursor_name
   While there are rows to fetch
      FETCH NEXT FROM cursor_name
   CLOSE cursor_name
   DEALLOCATE cursor_name

The real power of cursors is that the FETCH command can be used to scroll around within the result set, not just going through one row at a time. The following table shows what the application can do with different FETCH commands:


Command                  Result
------------------------------------------------------------------------------
FETCH FIRST              Positions the cursor on the first row

FETCH RELATIVE X         Jumps the cursor  X rows from the current position

FETCH ABSOLUTE Y         Positions the cursor Y rows from the start of a
                         result set

FETCH PRIOR              Reads the row just before the current row

Once positioned on a row in a cursor, an application can work with the data from that row.

Some cursors also support UPDATE operations, and applications can perform an UPDATE on the row where the cursor is currently positioned. In the case of a cursor built using a SELECT that references multiple tables, the UPDATE command can only reference one of the tables. Therefore, the operation affects only the table actually named in the UPDATE command. For example, in the following operation, only one row in some_table_b would be deleted; no rows in some_table_a would be affected:


   DECLARE cursor_name CURSOR FOR
      select *
      from some_table_a A, some_table_b B
      where A.PrimaryKey = B.ForeignKey
   OPEN cursor_name
   FETCH FIRST FROM cursor_name
   DELETE FROM some_table_b WHERE CURRENT OF cursor_name
   CLOSE cursor_name
   DEALLOCATE cursor_name

ODBC and DB-Library cursors are used by applications written in languages such as C, C++, or Visual Basic using the ODBC or DB-Library APIs. The server cursors introduced with SQL Server 6.0 are used in Transact-SQL scripts and stored procedures. They are also used to implement the cursors in ODBC and DB-Library applications. When either the SQL Server ODBC driver or a DB-Library application is asked to open a cursor, they implement the cursor by opening a server cursor of the same type. The ODBC or DB-Library application is not aware that underneath the API, it is actually working with server cursors.

The cursors defined in ANSI SQL only support operations that act upon one row at a time. The cursors defined in DB-Library and ODBC extend this functionality to support multiple row operations in cursors. ODBC does this by introducing the concept of a rowset size. If an ODBC application sets the rowset size option to 10, then each FETCH operation will retrieve 10 rows. DB-Library offers the same functionality through the nrows parameter of dbcursoropen().

The remainder of this article describes the operation of DB-Library and ODBC cursors; doing so requires defining all of the cursor options. After describing these cursors and defining the principles, it will then be easier to describe the simplified ANSI-style cursors available in Transact- SQL.

Originally, Microsoft DB-Library did not use the concept of cursors. An application ran a SELECT, and then read the rows back sequentially, one row at a time in a forward direction, until the end of the result set was reached. The result set did not support positioned updates; it was simply the stream of rows that came back from the SELECT. If an application wanted to UPDATE, INSERT, or DELETE one of the rows in the result set, it had to build an UPDATE command targeted at the base table referenced by the SELECT, and then reference the relevant key values in the where clause of the UPDATE command.

The version of DB-Library that came with SQL Server 4.2 for OS/2 introduced the DB-Library Cursor Library, which supported scrollable, updatable cursors. Starting with SQL Server 6.0, the DB-Library Cursor Library implemented its cursors by opening the server cursors introduced in SQL Server 6.0. A DB-Library application would still either open a standard result set, or use the Cursor Library if it wanted a scrollable, updatable cursor.

DB-Library classifies its cursors into three categories. A client cursor is one implemented on the client; these are the only cursors supported in SQL Server 4.21a and earlier. These cursors can also be implemented in SQL Server 6.0 and later, if the application sets the DBCLIENTCURSORS option. An explicit server cursor is implemented under SQL Server 6.0 and later, if the first FETCH is done using the extended function dbcursorfetchex(). A transparent server cursor is implemented under SQL Server 6.0 and later if the first FETCH is done using the standard dbcursorfetch(). There are some differences between transparent and explicit cursors when fetching rows. Refer to the "Programming DB-Library 6.0 for C" manual for the specific distinctions.

The ODBC API does not make a distinction between a result set and a cursor. In the ODBC model, all SQL commands return a result set, which an application fetches through using SQLFetch() or SQLExtendedFetch(). Before running a SQL command, an ODBC application can set statement options to control the cursor behavior of the result set. These options and their default settings are:


   Option                Default
   --------------------------------------

   SQL_CURSOR_TYPE       SQL_FORWARD_ONLY

   SQL_CONCURRENCY       SQL_READ_ONLY

   SQL_ROWSET_SIZE       1

These settings are identical to the characteristics of the default result set in DB-Library. If an ODBC application wants cursor behavior, it simply changes these options to the desired setting before running a SQL command. If an ODBC application runs a command with the default settings, the Microsoft SQL Server ODBC driver will process the result set the same way a DB-Library application would process its standard result set. Prior to SQL Server 6.0, this was the only option supported by the Microsoft SQL Server ODBC driver, unless the application used the ODBC Cursor Library (see Appendix G of the ODBC 2.0 Programmer's Reference for more information on the ODBC Cursor Library). Starting with SQL Server 6.0, the Microsoft SQL Server ODBC driver supports the other cursor options by using the server cursors introduced with SQL Server 6.0. If any of these statement options are changed from their defaults, the Microsoft SQL Server ODBC driver implements the cursor functionality by opening a server cursor with the same characteristics.

ODBC applications also do not typically perform positioned updates using the WHERE CURRENT OF cursor_name syntax on UPDATE commands. Instead, ODBC applications typically use the ODBC API function SQLSetPos() for positioned updates. For more information, refer to the ODBC 2.0 Programmer's Guide.

Cursor Types

ODBC and DB-Library share the following five cursor types:

STATIC Cursors
ODBC: SQL_CURSOR_TYPE = SQL_CURSOR_STATIC
DB-Library: scrollopt = CUR_INSENSITIVE

In a static cursor, the complete result set is built when the cursor is opened, and the cursor will not reflect any changes made in the database that may affect either the rows that make up the result set, or the values in the columns of those rows. In other words, static cursors will still show rows in the result set that have been deleted from the database after the cursor was opened. If new rows have been inserted that satisfy the conditions of the cursor's SELECT, they will not appear in the cursor. If rows in the result set have been updated, the new data values will not appear in the cursor. No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made by the same user who opened the cursor. Static cursors are read-only.

DYNAMIC Cursors
ODBC: SQL_CURSOR_TYPE = SQL_CURSOR_DYNAMIC
DB-Library: scrollopt = CUR_DYNAMIC

Dynamic cursors are the opposite of static cursors; they reflect all changes made to the rows in their result set as the user scrolls around the cursor. In other words, the data values and membership of rows in the cursor can change dynamically on each FETCH. This includes all DELETE, INSERT, and UPDATE commands either made by the user who opened the cursor or committed by other users. Dynamic cursors do not support FETCH ABSOLUTE, because the size of the result set and the position of rows within the result set are not constant. The row that starts out as the tenth row in the result set may be the seventh row the next time a FETCH is performed.

FORWARD_ONLY Cursors
ODBC: SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY
DB-Library: scrollopt = CUR_FORWARD

This cursor is similar to a dynamic cursor, but it only supports fetching the rows serially in sequence.

KEYSET Cursors
ODBC: SQL_CURSOR_TYPE = SQL_CURSOR_KEYSET_DRIVEN
DB-Library: scrollopt = CUR_KEYSET

With a keyset cursor, the membership of rows in the result set and their order is fixed when the cursor is opened. Changes to data values in these rows (made by the current user or committed by other users) will be reflected in the rows as the user scrolls through the cursor. Changes that would affect either a row's membership or order in the result set, such as deletions, insertions, or updates of the key value, are not reflected unless the cursor is closed and reopened. Keyset cursors are controlled through a set of unique identifiers (keys), known as the keyset. The keyset is the set of all the key values that made up the rows in the result set when the cursor was opened. The cursor will reflect any changes made to columns that do not make up the keyset, but it will not reflect any changes that would affect the order or membership of the keyset.

MIXED Cursors
ODBC: SQL_CURSOR_TYPE = SQL_CURSOR_KEYSET_DRIVEN, SQL_KEYSET_SIZE = n
DB-Library: scrollopt = n (where 1 < n < number of rows in cursor)

Mixed cursors combine features of keyset and dynamic cursors. With a mixed cursor, the size of the keyset is less than the set of all the keys in the result set. For example, a mixed cursor with a result set of 1,000 rows can have a keyset size of 100. As long as the user scrolls within the same keyset, the cursor operates as a keyset cursor. However, if the user scrolls beyond the bounds of the current keyset, the cursor works as a dynamic cursor as it builds the new keyset. Once in the new keyset, the cursor once again operates as a keyset cursor until the user scrolls beyond the bounds of the new keyset.

All ODBC cursors support the concept of a rowset, which is the number of rows returned on individual fetches. For example, if an application is presenting a 10 row grid to the user, the cursor can be defined with a rowset size of 10, to simplify mapping data into the grid. DB-Library implements the same concept, using the fetch buffer size on its cursors.

Concurrency Options

In addition to the cursor types, cursor operations are also affected by the concurrency options set by the application:

ODBC: SQL_CONCURRENCY = SQL_CONCUR_READONLY
DB-Library: concuropt = CUR_READONLY

With this option set, the cursor will not support UPDATE commands. Locks are not held on the underlying rows that make up the result set.

ODBC: SQL_CONCURRENCY = SQL_CONCUR_VALUES
DB-Library: concuropt = CUR_OPTCCVAL

This is the first option offering optimistic concurrency control. Optimistic concurrency control is a standard part of transaction control theory, and is discussed in most papers and books on the subject. The user or application chooses optimistic control when "optimistic" that there is a slight chance that anyone else may have updated a row in the interval between when the cursor is opened and when the row is updated. When the cursor is opened in this mode, no locks are held on the underlying rows, in order to maximize throughput. If the user attempts an UPDATE, SQL Server compares the current values in the row with the values retrieved when the cursor was opened. If any of the values have changed, the engine knows that someone else has already updated the row, and it returns an error. If the values are the same, the cursor engine performs the UPDATE. Selecting this option causes the user or application to accept the responsibility of dealing with an occasional error indicating that another user updated the row and changed the values. A typical action taken by an application that receives this error would be to refresh the cursor, to get the new values, and then let the user decide if he or she still wants to perform the UPDATE on the new values.

ODBC: SQL_CONCURRENCY = SQL_CONCUR_ROWVER
DB-Library: concuropt = CUR_OPTCC

This is the second optimistic concurrency control option, based on row versioning. With row versioning, the underlying table must have a version identifier of some type that the cursor engine can use to determine whether the row has been changed since it was read into the cursor. In SQL Server, this is the facility offered by the timestamp datatype. SQL Server timestamps are binary numbers that indicate the relative sequence of modifications in a database. Each database has a global current timestamp value. Each time a page in the database is modified in any way, SQL Server stores the current value in the page, and then increments it. If a table has a timestamp column, then the timestamps are taken down to the row level. Each time any row is modified, its timestamp column is updated with the current timestamp.

The cursor engine can then compare a row's current timestamp value with the timestamp value that was first retrieved into the cursor, to determine whether the row has been updated. The engine does not have to compare the values in all columns, only the timestamp. If an application requests SQL_CONCUR_ROWVER (ODBC) or CUR_OPTCC (DB-Library) on a table that does not have a timestamp column, the cursor will default to the values-based optimistic concurrency control, SQL_CONCUR_VALUES or CUR_OPTCCVAL.

ODBC: SQL_CONCURRENCY = SQL_CONCUR_LOCK
DB-Library: concuropt = CUR_LOCKCC

This option implements pessimistic concurrency control, in which the application attempts to lock the underlying database rows at the time they are read into the cursor result set. For cursors using server cursors, an update intent lock is placed on the row when it is read into the cursor. If the cursor is opened within a transaction, these intent-to-update locks are held until the transaction is terminated (committed or rolled back). If the cursor has been opened outside a transaction, the lock is dropped immediately after the row has been read into the cursor. Thus, applications wanting full pessimistic concurrency control would typically open the cursor within a transaction. An update intent lock prevents any other task from acquiring an update intent or exclusive lock; this prevents any other task from updating the row. An update intent lock, however, will not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update intent lock.

Please note that in all of these concurrency options, when any row in the cursor is updated, SQL Server will lock it with an exclusive lock. If the update has been done within a transaction, the exclusive lock will be held until the transaction is terminated. If the update has been done outside of a transaction, the update is automatically committed when it is completed and the exclusive lock is freed. Because SQL Server must acquire an exclusive lock before it updates the row, updates done through a cursor (just like standard updates) can be blocked by other connections holding a share lock on the row.

Transaction Isolation Levels

The full locking behavior of cursors is based on an interaction between the concurrency options discussed above and the transaction isolation level set by the client. ODBC clients set the transaction isolation level by setting the connection option SQL_TXN_ISOLATION. DB-Library applications and Transact-SQL scripts set the isolation level by running the Transact-SQL SET TRANSACTION ISOLATION LEVEL command. Users should combine the read locking behaviors of the combination of the concurrency and transaction isolation level options to determine the full locking behavior of a specific cursor environment.

READ COMMITTED (The default for both SQL Server and ODBC)
ODBC: SQL_TXN_ISOLATION = SQL_TXN_READ_COMMITED
DB-Library: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SQL Server acquires a shared lock while reading a row into a cursor, but frees the lock immediately after reading the row. Because a shared lock request will be blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed.

READ UNCOMMITTED
ODBC: SQL_TXN_ISOLATION = SQL_TXN_READ_UNCOMMITED
DB-Library: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SQL Server requests no locks while reading a row into a cursor, and honors no exclusive locks. This means that cursors can be populated with values that have already been updated, but not yet committed. The user is bypassing all of SQL Server's locking transaction control mechanisms.


REPEATABLE READ or SERIALIZABLE
ODBC: SQL_TXN_ISOLATION = SQL_TXN_REPEATABLE_READ_UNCOMMITED
   or SQL_TXN_ISOLATION = SQL_TXN_SERIALIZABLE
DB-Library: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
         or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SQL Server still requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction, instead of being freed after the row is read. This is the same effect as specifying HOLDLOCK on a select command.

Note that the ODBC API specifies additional transaction isolation levels, but these are not supported by SQL Server or the SQL Server ODBC Driver.

Transact-SQL Cursor Types

The ODBC and DB-Library cursor types above offer extensions beyond the ANSI cursor model, primarily to support the functions needed in interactive applications. The server cursors in Transact-SQL are implemented to the ANSI cursor standard, so they do not offer all the options of ODBC and DB-Library cursors. Because Transact-SQL cursors are used in stored procedures and triggers, they do not need the interactive extensions in DB-Library and ODBC cursors.

The following characteristics of Transact-SQL cursors are set when the cursor is declared:

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

The type of cursor implemented depends on the combination of SCROLL and INSENSITIVE options specified.

DECLARE cursor_name INSENSITIVE CURSOR ...

The resulting cursor will be an insensitive, or static, cursor. It will also be forward_only and read_only.

DECLARE cursor_name INSENSITIVE SCROLL CURSOR ...

The resulting cursor will be similar to the INSENSITIVE cursor; the only difference is that it will be scrollable instead of forward_only.

DECLARE cursor_name SCROLL CURSOR ...

The resulting cursor will be implemented as a keyset cursor. If there is no unique index on the underlying table the cursor will be read_only.

DECLARE cursor_name CURSOR ...

The resulting cursor will be implemented as a forward_only cursor.

In SQL Server 6.0, forward_only cursors default to keyset cursors. The cursor will be created as INSENSITIVE (read_only) if any of the following conditions are true:

DISTINCT, UNION, GROUP BY, and/or HAVING are used.

One or more of the underlying tables does not have a unique index.

An outer join is used.

A constant expression is included in the select_list.

In SQL Server 6.5, forward_only cursors default to dynamic cursors. If the cursor's select clause has an ORDER BY, the cursor will be created as INSENSITIVE, unless the underlying table has a unique index.

Additional query words: dblib db-lib tsql t-sql tran-sql

Keywords : kbusage SSrvDB_Lib SSrvGen SSrvTran_SQL
Version : 6.0
Platform : WINDOWS
Issue type : kbhowto