Database Reference
In-Depth Information
Server-Side Cursors
A server-side cursor is created on the SQL Server machine. It has the following characteristics:
It is created on the server machine.
The cursor metadata is maintained on the server machine.
It is created using either data access layers or T-SQL statements.
A server-side cursor created using T-SQL statements is tightly integrated with SQL Server.
It can be any type of cursor. (Cursor types are explained later in the chapter.)
the cost comparison between client-side and server-side cursors is covered later in the chapter in the
“Cost Comparison on Cursor type” section.
Note
Cursor Concurrency
Depending on the required degree of isolation and synchronization with the underlying content, cursors can be
classified into the following concurrency models:
Read-only. A nonupdatable cursor
Optimistic. An updatable cursor that uses the optimistic concurrency model (no locks retained
on the underlying data rows)
Scroll locks: An updatable cursor that holds a lock on any data row to be updated
Read-Only
A read-only cursor is nonupdatable; no locks are held on the base tables. While fetching a cursor row, whether an (S)
lock will be acquired on the underlying row depends upon the isolation level of the connection and any locking hints
used in the SELECT statement for the cursor. However, once the row is fetched, by default the locks are released. The
following T-SQL statement creates a read-only T-SQL cursor:
DECLARE MyCursor CURSOR READ_ONLY
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
The lowest-level locking overhead makes the read-only type of cursor faster and safer. Just remember that you
cannot manipulate data through the read-only cursor, which is the sacrifice you make for performance.
Optimistic
The optimistic with values concurrency model makes a cursor updatable. No locks are held on the underlying
data. The factors governing whether an (S) lock will be acquired on the underlying row are the same as for a
read-only cursor.
 
 
Search WWH ::




Custom Search