For read-only cursors, queries at isolation level 0 (dirty reads) require a unique index. Read-only cursors at isolation level 1 or 3 should produce the same query plan as the select statement outside of a cursor.
The index requirements for updatable cursors mean that updatable cursors may use different query plans than read-only cursors. Update cursors have these indexing requirements:
If the cursor is not declared for update, a unique index is preferred over a table scan or a nonunique index.
If the cursor is declared for update without a for update of list, a unique index is required on allpages-locked tables. An error is raised if no unique index exists.
If the cursor is declared for update with a for update of list, then only a unique index without any columns from the list can be chosen on an allpages-locked table. An error is raised if no unique index qualifies.
When cursors are involved, an index that contains an IDENTITY column is considered unique, even if the index is not declared unique. In some cases, IDENTITY columns must be added to indexes to make them unique, or the optimizer might be forced to choose a suboptimal query plan for a cursor query.
