The behavior of a delete or update command to the base table for a cursor on a single-table query depends on the type of modification, the locking scheme of the base table, and whether the clustered index of the base table is affected:
A positioned or searched delete command that deletes the row at the cursor location positions the cursor to the next qualifying row on the table. This is true for both allpages-locked and data-only-locked tables. A subsequent positioned update or delete using the same cursor is not allowed until the next fetch positions the cursor on the next row that qualifies.
A searched or positioned update command that does not change the position of the row leaves the current position of the cursor unchanged. The next fetch returns the next qualifying row.
A searched or positioned update command on an allpages-locked table can change the location of the row; for example, if it updates key columns of a clustered index. The cursor does not track the row; the cursor remains positioned before the next row at the original location. Positioned updates are not allowed until a subsequent fetch returns the next row. The updated row may be visible to the cursor a second time, if the row moves to a later position in the search order.
Data-only-locked tables have fixed row IDs, so expanding updates or updates that affect the clustered key do not move the location of the row. The cursor remains positioned on the row, and the next fetch returns the next qualifying row.
This cursor positioning behavior is unchanged from versions earlier than 11.9.2.
