Static Cursor and Where current of
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 each UPDATE. 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:


  CURRENT OF  
  
  Specifies that the update is performed at the current position of the specified cursor.  
  
  A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE  clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.


Test Setup

SET NOCOUNT ON;
-- DROP TABLE ##CursorTest;
CREATE TABLE ##CursorTest ([ID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
                           [Val] INT NOT NULL);
INSERT INTO ##CursorTest ([Val]) VALUES (1), (1), (1), (1);


Updateable CURSOR and WHERE CURRENT OF

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  KEYSET -- removing only reduces logical reads by 4
  SCROLL_LOCKS
  --OPTIMISTIC 
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] &lt; 5
  FOR UPDATE OF [Val];

DECLARE @ID INT;
OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE CURRENT OF curTest;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;


Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 1, logical reads 2


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 be OPTIMISTIC increased the Logical Reads.

STATIC Cursor and standard UPDATE

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  STATIC
  OPTIMISTIC 
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] &lt; 5;

DECLARE @ID INT;

OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE  tmp.[ID] = @ID;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;


Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2


These simple tests seem to show the STATIC Cursor and regular UPDATE 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.