After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.

Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.

This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.

I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the conditions
will still show execute as described above.

Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.

Thanks in advance.