While updating a column in a table the last day I was shown a new technique by our Senior DBA to reduce reads and contention.
The pseudo-code is
WHILE [always true condition] BEGIN UPDATE [...] IF @@ROWCOUNT = 0 BREAK; END
However while running this, I had accidently left Include Actual Execution Plan on so in the Messages tab I was seeing repeating occurrences of this:
(1000 row(s) affected) (1 row(s) affected)
However the code worked and the WHILE loop worked!
My question is, if including the Actual Execution Plan returns 1 row affected, shouldn't this create an infinite loop?
Why is it not getting counted?
Examples tables below:
USE [tempdb]; GO CREATE TABLE [dbo].[UpdateMePlease] ([ID] INT IDENTITY(1, 1), [UselessNote] VARCHAR(10) DEFAULT 'Delete me!', [UpdateValue] VARCHAR(10) NULL) GO INSERT INTO [dbo].[UpdateMePlease] ([UselessNote]) DEFAULT VALUES; GO 10000 SELECT * FROM [dbo].[UpdateMePlease]; /* turn Actual Execution Plan on here */ WHILE 1 = 1 BEGIN UPDATE TOP (1000) [dbo].[UpdateMePlease] SET [UpdateValue] = [UselessNote] WHERE [UpdateValue] IS NULL; IF @@ROWCOUNT = 0 BREAK END -- Clean up DROP TABLE [dbo].[UpdateMePlease];