The declare cursor statement must precede any open statement for that cursor. You cannot combine declare cursor with other statements in the same Transact-SQL batch, except when using a cursor in a stored procedure.
The select_statement is the query that defines the cursor result set. See Reference Manual: Commands. In general, select_statement can use nearly the full syntax and semantics of a Transact-SQL select statement, including the holdlock keyword. However, it cannot contain a compute, for browse, or into clause.
You can use either insensitive or semi_sensitive to explicitly specify cursor sensitivity.
An insensitive cursor is a snapshot of the result set, taken when the cursor is opened. An internal worktable is created and fully populated with the cursor result set when you open the cursor.
Any locks on the base tables are released, and only the worktable is accessed when you execute fetch. Any data changes in the base table on which the cursor is declared do not affect the cursor result set. The cursor is read-only, and cannot be used with for update.
In a semi_sensitive cursor, some data changes in the base tables may appear in the cursor. The query plan chosen and whether the data rows have been fetched at least once may affect the visibility of the base table data change.
semi_sensitive scrollable cursors are like insensitive cursors, in that they use a worktable to hold the result set for scrolling purposes. In semi_sensitive mode, the cursor’s worktable materializes as the rows are fetched, rather than when you open the cursor. The membership of the result set is fixed only after all the rows have been fetched once, and copied to the scrolling worktable.
If you do not specify cursor sensitivity, the default value is semi_sensitive.
Even if you declare a cursor semi_sensitive, the visibility of data changes in the base table of the cursor depends on the query plan chosen by the optimizer.
Any sort command forces the cursor to become insensitive, even if you have declared it semi_sensitive, because it requires the rows in a table to be ordered before sort can be executed. A worktable, however, can be populated before any rows can be fetched.
For example, if a select statement contains an order by clause, and there is no index on the order by column, the worktable is fully populated when the cursor is opened, whether or not you declare the cursor to be semi_sensitive. The cursor becomes insensitive.
Generally, rows that have not yet been fetched can display data changes, while rows that have already been fetched do not.
The main benefit of using a semi_sensitive scrollable cursor instead of an insensitive scrollable cursor is that the first row of the result set is returned promptly to the user, since the table lock is applied row by row. If you fetch a row and update it, it becomes part of the worktable through fetch, and the update is executed on the base table. There is no need to wait for the result set worktable to be fully populated.
You can use either scroll or no scroll to specify cursor_scrollability. If the cursor is scrollable, you can scroll through the cursor result set by fetching any, or many rows back and forth; you can also scan the result set repeatedly.
All scrollable cursors are read-only, and cannot be used with for update in a cursor declaration.
The read_only option specifies that the cursor result set cannot be updated. In contrast, the for update option specifies that the cursor result set is updatable. You can specify of column_name_list after for update with the list of columns from the select_statement defined as updatable.
