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.
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.
DECLARE titles_cursor CURSOR FOR
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id — Variable value from the outer cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"
WHILE @@FETCH_STATUS = 0
SELECT @message = " " + @title
FETCH NEXT FROM titles_cursor INTO @title
The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. If rows making up the result set are updated by other users, the new data values are not displayed in the static cursor. The static cursor displays rows deleted from the database after the cursor was opened. No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.Microsoft® SQL Server™ static cursors are always read-only.Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor, except for the case where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, Microsoft® SQL Server™ 2000 does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors. Transact-SQL cursors support forward-only static, keyset-driven, and dynamic cursors. The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
Changes to data values in nonkeyset columns (made by the cursor owner or committed by other users) are visible as the user scrolls through the cursor. Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened. Inserts made through the cursor using an API function such as the ODBC SQLSetPos function are visible at the end of the cursor. @@FETCH_STATUS returns a "row missing" status when an attempt is made to fetch a row deleted after the cursor was opened. An update to a key column operates like a delete of the old key value followed by an insert of the new key value. The new key value is not visible if the update was not made through the cursor; it is visible at the end of the cursor if the update was made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause and the SELECT statement did not contain a JOIN condition in the FROM clause. The new key value is not visible if the insert contained a remote table in the FROM clause. Attempts to retrieve the old key value get the same missing row fetch status as a deleted row.