Database Reference
In-Depth Information
The following T-SQL statement creates a static T-SQL cursor:
DECLARE MyCursor CURSOR STATIC
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
Some tests show that a static cursor can perform as well as—and sometimes faster than—a forward-only cursor.
Be sure to test this behavior on your own system.
Keyset-Driven Cursors
These are the characteristics of keyset-driven cursors:
Keyset cursors are controlled by a set of unique identifiers (or keys) known as a
keyset. The
keyset is built from a set of columns that uniquely identify the rows in the result set.
These cursors create the keyset of rows in the
tempdb database when the cursor is opened.
Membership of rows in the cursor is limited to the keyset of rows created in the
tempdb
database when the cursor is opened.
On fetching a cursor row, the database engine first looks at the keyset of rows in
tempdb
and then navigates to the corresponding data row in the underlying tables to retrieve the
remaining columns.
They support all scrolling options.
Keyset cursors allow all changes through the cursor. An
INSERT performed outside the cursor
is not reflected in the cursor, since the membership of rows in the cursor is limited to the
keyset of rows created in the tempdb database on opening the cursor. An INSERT through the
cursor appears at the end of the cursor. A DELETE performed on the underlying tables raises
an error when the cursor navigation reaches the deleted row. An UPDATE on the nonkeyset
columns of the underlying tables is reflected in the cursor. An UPDATE on the keyset columns
is treated like a DELETE of an old key value and the INSERT of a new key value. If a change
disqualifies a row for membership or affects the order of a row, then the row does not
disappear or move unless the cursor is closed and reopened.
The following T-SQL statement creates a keyset-driven T-SQL cursor:
DECLARE MyCursor CURSOR KEYSET
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
 
Search WWH ::




Custom Search