Database Reference
In-Depth Information
Dynamic Cursors
These are the characteristics of dynamic cursors:
•
Dynamic cursors operate directly on the base tables.
•
The membership of rows in the cursor is not fixed, since they operate directly on the
base tables.
•
As with forward-only cursors, rows from the underlying tables are not retrieved until the
cursor rows are fetched using a cursor
FETCH
operation.
FETCH ABSOLUTE n
, since
•
Dynamic cursors support all scrolling options except
the membership of rows in the cursor is not fixed.
•
These cursors allow all changes through the cursor. Also, all changes made to the underlying
tables are reflected in the cursor.
•
Dynamic cursors don't support all properties and methods implemented by the database
API cursors. Properties such as
AbsolutePosition
,
Bookmark
, and
RecordCount
, as well as
methods such as
clone
and
Resync
, are not supported by dynamic cursors. Instead, they are
supported by keyset-driven cursors.
The following T-SQL statement creates a dynamic T-SQL cursor:
DECLARE MyCursor CURSOR DYNAMIC
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
The dynamic cursor is absolutely the slowest possible cursor to use in all situations. It takes more locks and holds
them longer, which radically increases its poor performance. Take this into account when designing your system.
Cursor Cost Comparison
Now that you've seen the different cursor flavors, let's look at their costs. If you must use a cursor, you should always
use the lightest-weight cursor that meets the requirements of your application. The cost comparisons among the
different characteristics of the cursors are detailed next.
Cost Comparison on Cursor Location
The client-side and server-side cursors have their own cost benefits and overhead, as explained in the sections
that follow.
Client-Side Cursors
Client-side cursors have the following cost benefits compared to server-side cursors:
•
Higher scalability:
Since the cursor metadata is maintained on the individual client machines
connected to the server, the overhead of maintaining the cursor metadata is taken up by the
client machines. Consequently, the ability to serve a larger number of users is not limited by
the server resources.
