• Article

Cursors (Database Engine)

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursors extend result processing by:

  • Allowing positioning at specific rows of the result set.

  • Retrieving one row or block of rows from the current position in the result set.

  • Supporting data modifications to the rows at the current position in the result set.

  • Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

  • Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

Requesting a Cursor

Microsoft SQL Server supports two methods for requesting a cursor:

  • Transact-SQL

    The Transact-SQL language supports a syntax for using cursors modeled after the ISO cursor syntax.

  • Database application programming interface (API) cursor functions

    SQL Server supports the cursor functionality of these database APIs:

    • ADO (Microsoft ActiveX Data Object)

    • OLE DB

    • ODBC (Open Database Connectivity)

An application should never mix these two methods of requesting a cursor. An application that has used the API to specify cursor behaviors should not then execute a Transact-SQL DECLARE CURSOR statement to also request a Transact-SQL cursor. An application should only execute DECLARE CURSOR if it has set all the API cursor attributes back to their defaults.

If neither a Transact-SQL nor API cursor has been requested, SQL Server defaults to returning a complete result set, known as a default result set, to the application.

Cursor Process

Transact-SQL cursors and API cursors have different syntax, but the following general process is used with all SQL Server cursors:

  1. Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.

  2. Execute the Transact-SQL statement to populate the cursor.

  3. Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.

  4. Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.

  5. Close the cursor.