Export (0) Print
Expand All

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

NoteNote

Fast forward cursors are never converted.

NoteNote

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft