Using Implicit Cursor Conversions
Applications can request a cursor type and then execute a Transact-SQL statement that is not supported by server cursors of the type requested. Microsoft SQL Server returns an error that indicates the cursor type has changed. This conversion is called implicit cursor conversion, and is sometimes referred to as cursor degradation.
These are the factors that trigger SQL Server to implicitly convert a cursor from one type to another.
Step |
Conversion triggered by |
Forward-only |
Keyset-driven |
Dynamic |
Go to step |
|---|---|---|---|---|---|
1 |
Query FROM clause references no tables. |
Becomes static. |
Becomes static. |
Becomes static. |
Done |
2 |
Query contains: select list aggregates GROUP BY UNION DISTINCT HAVING |
Becomes static. |
Becomes static. |
Becomes static. |
Done |
3 |
Query generates an internal work table, for example the columns of an ORDER BY are not covered by an index. |
Becomes keyset. |
|
Becomes keyset. |
5 |
4 |
Query references remote tables in linked servers. |
Becomes keyset. |
|
Becomes keyset. |
5 |
5 |
Query references at least one table without a unique index. Transact-SQL cursors only. |
|
Becomes static. |
|
Done |
Note
Fast forward cursors are never converted.
Note
Keyset and dynamic cursors are only converted if any of the underlying base tables do not have a unique index, or if the query does not return the key columns of the base tables directly. For example, if the query contains aggregate functions or set operators.