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

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.

See Also

Other Resources

Implicit Cursor Conversions (ODBC)

Help and Information

Getting SQL Server 2005 Assistance