2

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];
7

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution plans are for monitoring and reviewing, not for influencing actual behavior.

Note that the inverse is also not necessarily a given - not all things that will populate @@ROWCOUNT will lead to the DONE_IN_PROC message, e.g.

DECLARE @foo int, @x varchar(32) = 'hello';
SET @foo = @@ROWCOUNT;
PRINT @foo;

This will yield 1 in the messages pane, but you will not see 1 row(s) affected.

Now, I've always been a big fan of using SET NOCOUNT ON;, which renders this question rather moot. But it seems the impact of DONE_IN_PROC in modern versions is not what it used to be when I formed this opinion:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.