Fetching and Scrolling

  • Article

The operation to retrieve a row from a cursor is called a fetch. These are the fetch options:

  • FETCH FIRST
    Fetches the first row in the cursor.
  • FETCH NEXT
    Fetches the row after the last row fetched.
  • FETCH PRIOR
    Fetches the row before the last row fetched.
  • FETCH LAST
    Fetches the last row in the cursor.
  • FETCH ABSOLUTE n
    Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.
  • FETCH RELATIVE n
    Fetches the row n rows from the last row fetched. If n is positive, the row n rows after the last row fetched is fetched. If n is negative, the row n rows before the last row fetched is fetched. If n is 0, the same row is fetched again.

When a cursor is opened, the current row position in the cursor is logically before the first row. This causes the different fetch options to have the following behaviors if they are the first fetch performed after the cursor is opened:

  • FETCH FIRST
    Fetches the first row in the cursor.
  • FETCH NEXT
    Fetches the first row in the cursor.
  • FETCH PRIOR
    Does not fetch a row.
  • FETCH LAST
    Fetches the last row in the cursor.
  • FETCH ABSOLUTE n
    Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, then the row n rows before the end of the cursor is fetched (for example, n = -1 returns the last row in the cursor). If n is 0, no rows are fetched.
  • FETCH RELATIVE n
    Fetches the nth row in the cursor if n is positive. No rows are fetched if n is negative or 0.

Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch. A cursor that supports fetching multiple rows at a time is called a block cursor.

Cursor Classifications

A cursor can be classified by the fetch options it supports:

  • Forward-only
    Rows must be fetched serially from the first row to the last row. FETCH NEXT is the only fetch operation allowed.
  • Scrollable
    Rows can be randomly fetched from anywhere in the cursor. All the fetch operations are allowed (except that dynamic cursors do not support fetch absolute).

Scrollable cursors are especially useful for supporting online applications. A cursor can be mapped to a grid or list box in the application. As the user scrolls up and down and all around the grid, the application uses scroll fetches to retrieve the rows from the cursor the user wants to see.

APIs for Fetching Rows

The APIs for the actual statements, functions, or methods used have different names to fetch rows:

  • Transact-SQL cursors use the FETCH FIRST, FETCH LAST, FETCH NEXT, FETCH PRIOR, FETCH ABSOLUTE(n), and FETCH RELATIVE(n) statements.
  • 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 Recordset methods 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.

See Also

Other Resources

Fetching Rows
Scrolling and Fetching Rows
Cursor Functions (Transact-SQL)
FETCH (Transact-SQL)
Using Cursors

Help and Information

Getting SQL Server 2005 Assistance