Rowcount and multiple assignment

Question

Rowcount and multiple assignment

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 3

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

webrunner SSC-Dedicated Points: 31863 More actions · Answer 4

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

Rune Bivrin SSCrazy Eights Points: 8512 More actions · Answer 5

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.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 6

Well, I thought I'd @@ROWCOUNT in the bag until this came along 😛

Nice question.

honza.mf SSCertifiable Points: 5519 More actions · Answer 7

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

See, understand, learn, try, use efficient
© Dr.Plch

Craig@Work SSCrazy Points: 2346 More actions · Answer 8

I enjoyed working this one out, line by line 🙂

Good little puzzle to start the day. Thanks!

Craig@Work SSCrazy Points: 2346 More actions · Answer 9

? ... 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."

alitchfield SSC Journeyman Points: 76 More actions · Answer 10

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!

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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