Defining Cursors

Standard cursors can be used to scroll through and update a results set. Standard cursors do not require a unique index for SQL Server version 6.5; do not require a timestamp or a second connection to a database for updates, and do not create a copy of the entire results set. The following tables summarize these differences.

Standard cursors will continue to be enhanced in later versions; however, browse cursors will not.


Standard cursor	Description

Connections to the server	All share the same connection.
Timestamp column	Not required.
Transaction behavior	All cursors share the same transaction space. Standard cursors cannot block each other.
Unique index	Not required for SQL Server version 6.5.
Data currency	Data values are always current. Row membership depends on the SCROLLOPTION setting:
	If keyset-driven, membership is fixed at cursor-open time.
	If dynamic, membership changes.
Restrictions on use of SQL language	Cannot be used on stored procedures. Cannot contain UNION, GROUP BY, and HAVING clauses. In SQL Server version 6.5, dynamic cursors can contain an ORDER BY clause if the table has a unique index.


Browse cursor	Description

Connections to the server	One per cursor, plus one base connection for all updates.
Timestamp column	Required for positioned update or delete operations.
Transaction behavior	Read-only cursors can block updates that are made by the same program.
Unique index	Required for update only.
Data currency	For read-only cursors, all data is current.
	For updatable cursors, the server takes a snapshot of the data and stores it in the tempdb database when the cursor is opened.
Restrictions on use of SQL language	Updatable cursors require the FOR BROWSE clause.