Hugo Kornelis (7/24/2008)
Gianluca Sartori (7/24/2008)
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...Hi Gianluca,
I agree that the words "need to change the data in the cursor rows" (emphasis added) can be a little confusing. With hindsight, I should have formulated this as "need to change the data retrieved by the cursor" or something similar.
But the three answer options given all do the same thing: update data in the underlying table. So even if there does exist some way to update just the cursor without updating the underlying data (which I doubt, given that a cursor is in fact a positioning mechanism and not a data colection), it should have been clear from the answer options that the intention is to change the data in the underlying table.
Hi Hugo, thanks for your reply!
I don't know how SQLServer handles internally the updating of the cursor using CURRENT OF, but I think it simply identifies the row to update in the underlying table from the position in cursor, without issuing additional statements.
I found myself working with an old VB6 application some years ago, using DAO to access data. DAO handles server-side cursors better than client-side cursors and I used to check for long running queries with profiler: updates to a recordset resulted in some sort of sp_cursor in profiler.
I don't know exactly what sqlserver does when you update with CURRENT OF, but I would expect something similar to this.
I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.
You might wish to check out the question for July 8, which deals with FAST_FORWARD and other options for fast (readonly) cursors.
I'm one of those who would have answered "it depends", if only the option was there.
-- Gianluca Sartori