April 14, 2010 at 9:41 am
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
April 14, 2010 at 10:27 am
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
April 14, 2010 at 10:58 am
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
April 14, 2010 at 12:24 pm
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
April 14, 2010 at 1:32 pm
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.
April 14, 2010 at 2:49 pm
Well, I thought I'd @@ROWCOUNT in the bag until this came along 😛
Nice question.
April 15, 2010 at 1:20 am
rjv_rnjn (4/14/2010)
Well, I thought I'd @@ROWCOUNT in the bag until this came along 😛Nice 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
April 15, 2010 at 1:54 am
I enjoyed working this one out, line by line 🙂
Good little puzzle to start the day. Thanks!
April 15, 2010 at 1:56 am
? ... 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."
April 16, 2010 at 8:59 am
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!
April 17, 2010 at 5:30 am
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
April 18, 2010 at 5:06 am
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.
April 18, 2010 at 9:55 am
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