Rune Bivrin (4/14/2010)
It's important to note that SET @result = cast (@@rowcount as varchar) does NOT change @@ROWCOUNT. The first 1 comes from the last INSERT #n VALUES(3).This is one of the important differences between SELECT and SET when assigning variables. SET never yields a rowcount, and thus doesn't change @@ROWCOUNT.
Someone may already have said this, but in case they haven't:
That is very wrong (but it doesn't make a difference to the answer in this case because a SET @localvariable statement sets @@rowcount to 1).
From BoL (http://msdn.microsoft.com/en-gb/library/ms187316.aspx):
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.
This can be demonstrated by a small adaptation to the code of the question:declare @result varchar (5)
create table #n (n int)
insert #n values (1),(2),(2)
set @result = cast (@@rowcount as varchar)
/*SET NOCOUNT OFF*/
select @result = @result + cast (@@rowcount as varchar) from #n
select @result + cast (@@rowcount as varchar)
drop table #n
which will deliver 31113, not 33333 as would be the case if SET did not set @@rowcount.
Tom