Database Reference
In-Depth Information
Forward-Only Cursors
These are the characteristics of forward-only cursors:
•
They operate directly on the base tables.
•
Rows from the underlying tables are usually not retrieved until the cursor rows are fetched
using the cursor
FETCH
operation. However, the database API forward-only cursor type, with
the following additional characteristics, retrieves all the rows from the underlying table first:
•
Client-side cursor location
•
Server-side cursor location and read-only cursor concurrency.
FETCH NEXT
) through the cursor.
•
They support forward scrolling only (
INSERT
,
UPDATE
, and
DELETE
) through the cursor. Also, these cursors
reflect all changes made to the underlying tables.
•
They allow all changes (
The forward-only characteristic is implemented differently by the database API cursors and the T-SQL cursor.
The data access layers implement the forward-only cursor characteristic as one of the four previously listed cursor
types. But the T-SQL cursor doesn't implement the forward-only cursor characteristic as a cursor type; rather, it
implements it as a property that defines the scrollable behavior of the cursor. Thus, for a T-SQL cursor, the forward-only
characteristic can be used to define the scrollable behavior of one of the remaining three cursor types.
A forward-only cursor with a read-only property can be created using a
fast forward
statement. The T-SQL
syntax provides a specific cursor type option,
FAST_FORWARD
, to create a fast-forward-only cursor. The nickname for
the
FAST_FORWARD
cursor is the
fire hose
because it is the fastest way to move data through a cursor and because all the
information flows one way. However, don't be surprised when the “firehose” is still not as fast as traditional set-based
operations. The following T-SQL statement creates a fast-forward-only T-SQL cursor:
DECLARE MyCursor CURSOR FAST_FORWARD
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
The
FAST_FORWARD
property specifies a forward-only, read-only cursor with performance optimizations enabled.
Static Cursors
These are the characteristics of static cursors:
tempdb
database when the cursor is opened.
Thereafter, static cursors operate on the snapshot in the
tempdb
database.
•
They create a snapshot of cursor results in the
•
Data is retrieved from the underlying tables when the cursor is opened.
FETCH FIRST
,
FETCH NEXT
,
FETCH PRIOR
,
FETCH
LAST
,
FETCH ABSOLUTE n
, and
FETCH RELATIVE n
.
•
Static cursors support all scrolling options:
•
Static cursors are always read-only; data modifications are not allowed through static cursors.
Also, changes (
INSERT
,
UPDATE
, and
DELETE
) made to the underlying tables are not reflected in
the cursor.
