Rowcount and multiple assignment
-
sjimmo (4/14/2010)
Oleg,insert into #n
select
top 3 row_number() over (order by [object_id])
from sys.objects;
is not the same as
insert #n values (1)
insert #n values (2)
insert #n values (3)
Should you check the @@rowcount after performing your select, it will return 3, meaning that there were 3 rows affected by your SQL call.
In the individual inserts,it will return a 1 after the last insert command.
Yes, this is precisely what I tried to point out. In the original script the last insert as well as any insert before that set the @@rowcount to 1, and then the line
set @result = cast (@@rowcount as varchar);
did 2 things (set the variable and then reset the @@rowcount back to 1. So I changed the insert simply to reveal this behavior. The set-based insert set the @@rowcount to 3 and the line set @result = cast (@@rowcount as varchar); sets the @@rowcount to one as a result of the variable set.
Oleg
-
Nice question. It made me think and the explanation was good too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Good question. Made me realize that I need to study @@rowcount a bit more.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum -
Great question. I got it wrong - I correctly followed the logic of the script but had too little understanding of how ROWCOUNT gets set. Good stuff to know (and study!).
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected.
Ah, well. Learn something new everyday.
Just because you're right doesn't mean everybody else is wrong. -
Well, I thought I'd @@ROWCOUNT in the bag until this came along
Nice question.
-
rjv_rnjn (4/14/2010)
Well, I thought I'd @@ROWCOUNT in the bag until this came alongNice question.
Everytime I thought I'd something in the bag I realized I have overlooked some detail. Very often it's a very important detail:-D
See, understand, learn, try, use efficient
© Dr.Plch -
I enjoyed working this one out, line by line
Good little puzzle to start the day. Thanks!
-
? ... from BOL ...
"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'."
... and ..
"Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0."
-
I spend 30 precious minutes every Friday reading my SQL Server Central articles and attempting the QotD. This is the first time I cheated (IMHO) and ran the code for an answer. Like other people, the 111 was a surprise to me and is highly useful esoteric knowledge for future use... In short - excellent QotD!
-
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 #nselect @result + cast (@@rowcount as varchar)drop table #nwhich will deliver 31113, not 33333 as would be the case if SET did not set @@rowcount.
Tom
-
Tom.Thomson (4/17/2010)
Someone may already have said this, but in case they haven't:Actually, pretty much every other post so far has covered it :w00t: and Rune responded:
"Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected." Ah, well. Learn something new everyday.
-
Paul White NZ (4/18/2010)
Tom.Thomson (4/17/2010)
Someone may already have said this, but in case they haven't:Actually, pretty much every other post so far has covered it :w00t: and Rune responded:
"Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected." Ah, well. Learn something new everyday.
Yes, if I had read all the posts before firing up the editor I wouldn't have bothered.
Tom
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply