Static Cursor doesn't allow to modify the data because it's read only, and when executed with "Where current of", it returns error as expected.
So far so good.
But i was surprised to find that static cursor allow to modify data with variable like this.
DECLARE @nome varchar(100), @salario int,@idemp int DECLARE contact_cursor CURSOR STATIC FOR SELECT empno,ename, sal FROM emp
OPEN contact_cursor;
FETCH NEXT from contact_cursor into @idemp,@nome, @salario WHILE @@FETCH_STATUS=0 BEGIN
If @salario < 5000 Update Emp Set Sal = Sal * 1.1 where empno=@idemp --No error and do the update --Where current of contact_cursor; --gives error print @nome+' '+cast(@salario as varchar(100));
FETCH NEXT from contact_cursor into @idemp,@nome, @salario
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
The question is:
Which is the difference between using "where current" and the variable extracted with the cursor in this update?
Best Answer
The main difference seems to be how each approach finds the row to be updated. The
STATIC
Cursor copies the full result set to a hidden temporary table first (hence why it is read-only), so it would seem to be less efficient to then have to re-query the main table for eachUPDATE
. However, the Positioned Update seems to have quite a bit more in Logical Reads and operations. One advantage of the Positioned Update, however, is noted in the MSDN page for UPDATE:Test Setup
Updateable CURSOR and
WHERE CURRENT OF
Results:
Removing the
KEYSET
option did reduce the logical reads by 4 (I believe), but that might not be a savings on a more complicated query, possibly with JOINs.Also, switching
SCROLL_LOCKS
to beOPTIMISTIC
increased the Logical Reads.STATIC
Cursor and standardUPDATE
Results:
These simple tests seem to show the
STATIC
Cursor and regularUPDATE
being the better option, and a more complicated query for the Cursor might be an even bigger difference (assuming you are able to update based on the Clustered Key of the target table).But, if you have a situation where you can't narrow down to a individual row / have no Key value to use, then the Positioned Update would be quite handy.