When a SQL job starts, what is the value of @@ROWCOUNT?

Question

I have been looking for an answer to this question, but I believe it may be a useful piece of information for others as well.

I am working with TSQL in SQL server management studio. Due to the way our system processes information, it's desirable to do updates in smaller batches. A trick we use is to wrap updates in a while loop as such:

while (@@Rowcount <> 0)
begin
    update top (800) etc etc
end

I have created a job to do this update regularly and while this works in a query window, it does not seem to work in a job. Is the rowcount value populated when a job begins?

Answer 1

@@ROWCOUNT is 0 at the beginning of a statement, what is happening for you is that when SSMS first opens up a connection it executes a series of queries behind the scenes (you can capture the specific queries with a trace), so you get a residual value for @@ROWCOUNT of 1.

When doing batch updates like this, I tend to take a slightly different approach:

WHILE 1 = 1
BEGIN
    UPDATE  TOP (100) ....
    SET     ...


    IF @@ROWCOUNT = 0
        BREAK;
END

I don't think this has any benefit whatsoever over doing something like:

SELECT 1;
WHILE  @@ROWCOUNT > 0
BEGIN
    ...
END

And is more long winded, but doing a pointless select or assignment just feels odd to me, perhaps owing to some minor OCD.

Answer 2

@@Rowcount is system function with output INT NOT NULL --> default value for int is 0

You can get it by:

if @@rowcount = 0
    print 1
else 
    print 0

But don't try select @@rowcount at first row it's statements that make a simple assignment and is always set the @@ROWCOUNT value to 1. :)

So solution is add select 0 before your while. It works because @@rowcount of select 0 is one row..

select 0
while (@@Rowcount <> 0)
begin
    update top (800) etc etc
end

Answer 3

Saving the value of @@RowCount in a variable allows you to avoid any assumption about the initial value and any problems with losing the value when another statement is executed.

declare @Samples as Table ( Sample Int );
insert into @Samples ( Sample ) values ( 1 ), ( 2 ), ( 5 );

declare @RowCount as Int;
-- If there is any work to do then initialize   @RowCount   to   1 , otherwise   0 .
set @RowCount = case when exists ( select 42 from @Samples where Sample < 10 ) then 1 else 0 end;
declare @NewSample as Int, @OldSample as Int;

-- Loop through updating one row at a time.
while @RowCount > 0
  begin
  update Ph
    set @OldSample = Sample, @NewSample = Sample *= 2
    from ( select top 1 Sample from @Samples where Sample < 10 order by Sample ) as Ph
  set @RowCount = @@RowCount;
  -- Do what you will without losing the count of rows updated.
  select @RowCount as 'RowsProcessed', @OldSample as 'OldSample', @NewSample as 'NewSample'
  end