I tried running what you have and noticed one small error:
where @Title = '%Production%'
So if @Title ever actually = '%Production%' it will update the entire table. Until then it's going to update 0 rows. My guess is what you are trying to do is something like this:
WHERE @Title LIKE '%Production%'
AND Title = @Title
And on further inspection I don't think you want this either
SET @Department = 'Production'
You probably mean
SET Department = 'Production'
Which also means you don't need to be pulling Department
at all in your cursor. Last but not least, since you are updating the table that the cursor is pulling from I would use a READ ONLY cursor. This does however write the cursor results to tempdb so it may not be such a good idea if you have a large table.
Last comment: Cursors are not always bad. But they should only be used where batch processing is not possible. Say executing a stored procedure with multiple inputs. I think it's great that you are being proactive and practicing them however.
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] < 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] < 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.
Best Answer
YES, it is incorrect. The inner
FETCH NEXT
should go at the END of the loop. The code above would result in the following:Correcting the problem, and putting the
FETCH NEXT
at the end of the loop, results in the correct & expected output:Now to go submit this to RedGate...