Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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 2005 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

ms190641.note(en-US,SQL.90).gifNote:
Fast forward cursors are never converted.

ms190641.note(en-US,SQL.90).gifNote:
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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.