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.
These are the factors that trigger SQL Server to implicitly convert a cursor from one type to another.
| Step | Conversion triggered by | Forward-only | Fast-forward | Keyset-driven | Dynamic |
Go to step |
|---|---|---|---|---|---|---|
| 1 | Cursor references a view with a TOP clause. | Becomes static. | Becomes static. | Becomes static. | Becomes static. | Done |
| 2 | Query FROM clause references no tables. | Becomes static. | Becomes static. | Becomes static. | Done | |
| 3 | Query contains: select list aggregates GROUP BY UNION DISTINCT HAVING |
Becomes static. | Becomes static. | Becomes static. | Done | |
| 4 | Query references an inserted or deleted table within a trigger. | Becomes static. | Becomes static. | Becomes static. | Done | |
| 5 | Query joins a trigger table to another table. | Becomes static. | Done | |||
| 6 | READ_ONLY is not specified. | Becomes static. | 8 | |||
| 7 | ODBC API server cursor references text, ntext, or image columns. | Becomes dynamic. | 8 | |||
| 8 | Query generates an internal work table, for example the columns of an ORDER BY are not covered by an index. | Becomes keyset. | Becomes keyset. | 10 | ||
| 9 | Query references remote tables in linked servers. | Becomes keyset. | Becomes keyset. | Becomes keyset. | 10 | |
| 10 | Query references at least one table without a unique index. | Becomes static. | Done | |||
| 11 | Cursor references text, ntext, or image columns; and the query contains a TOP clause. | Becomes keyset. | Done |
SQL Server version 6.5 has this restriction in addition to the restrictions in SQL Server 7.0:
- If a dynamic cursor is requested and the Transact-SQL statement contains an ORDER BY that does not match an index or subquery, the cursor is converted to a keyset-driven or static cursor. If all the tables have a unique index, but no index that covers the ORDER BY, the cursor is converted to a keyset-driven cursor. If at least one table has no index that covers the ORDER BY and at least one has no unique index (not necessarily the same table), the cursor is converted to static.
An index column cannot be used to cover the ORDER BY if there are index columns to its left that are not referenced by the ORDER BY. For example, if an index is defined as using MyTable (LastName, FirstName), the index cannot be used to cover a statement using ORDER BY FirstName.
The SQL Server version 6.0 restriction regarding dynamic cursors is more simply defined:
- If a dynamic cursor is requested and there is at least one table that does not have a unique index, the cursor is converted to a static cursor.
If you are using API server cursors and get a message indicating the cursor type has been changed, you can call the following functions to see the type of cursor SQL Server opened:
- ODBC: Call SQLGetInfo for the SQL_CURSOR_TYPE attribute.
- DB-Library: Call dbcursorinfoex and refer to the Type field in the DBCURSORINFO structure returned by dbcursorinfoex.
- Transact-SQL: Use sp_describe_cursor and refer to the model and scrollable columns in the cursor returned by the procedure.