Database Reference
In-Depth Information
Forward-Only Cursors
These are the cost benefits of forward-only cursors:
•
Lower cursor open cost than static and keyset-driven cursors:
Since the cursor rows are not
retrieved from the underlying tables and are not copied into the
tempdb
database during
cursor open, the forward-only T-SQL cursor opens quickly. Similarly, the forward-only,
server-side API cursors with optimistic/scroll locks concurrency also open quickly since they
do not retrieve the rows during cursor open.
•
Lower scroll overhead:
Since only
FETCH NEXT
can be performed on this cursor type, it requires
less overhead to support different scroll operations.
•
Lower impact on the
tempdb
database than static and keyset-driven cursors:
Since the
forward-only T-SQL cursor doesn't copy the rows from the underlying tables into the
tempdb
database, no additional pressure is created on the database.
The forward-only cursor type has the following drawbacks:
•
Lower concurrency.
Every time a cursor row is fetched, the corresponding underlying row is
accessed with a lock request depending on the cursor concurrency model (as noted earlier in
the discussion about concurrency). It can block other users from accessing the resource.
•
No backward scrolling.
Applications requiring two-way scrolling can't use this cursor type.
But if the applications are designed properly, then it isn't difficult to live without backward
scrolling.
Fast-Forward-Only Cursor
The fast-forward-only cursor is the fastest and least expensive cursor type. This forward-only and read-only cursor is
specially optimized for performance. Because of this, you should always prefer it to the other SQL Server cursor types.
Furthermore, the data access layer provides a fast-forward-only cursor on the client side. That type of cursor uses
a so-called default result set to make cursor overhead almost disappear.
■
Note
the default result set is explained later in the chapter in the “Default result set” section.
Static Cursors
These are the cost benefits of static cursors:
•
Lower fetch cost than other cursor types:
Since a snapshot is created in the
tempdb
database
from the underlying rows on opening the cursor, the cursor row fetch is targeted to the
snapshot instead of the underlying rows. This avoids the lock overhead that would otherwise
be required to fetch the cursor rows.
•
No blocking on underlying rows:
Since the snapshot is created in the
tempdb
database, other
users trying to access the underlying rows are not blocked.
