Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Rowcount and multiple assignment Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 8:50 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:11 AM
Points: 658, Visits: 401
Very cool QotD -- thanks!
Post #903219
Posted Wednesday, April 14, 2010 9:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:58 PM
Points: 3,173, Visits: 1,267
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.


Are you sure about this? I tried the following on my server and did not get a result consistent with your statement above. I am using SQL 2005

declare @result int

select 1 union select 2
set @result = @@rowcount

select @@rowcount

I got

-----------
1
2


-----------
1




Post #903230
Posted Wednesday, April 14, 2010 9:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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.


I am sorry, but this is just plain wrong. Here is the proof showing that when I said that the line

set @result = cast (@@rowcount as varchar);

does 2 things (sets the value of @result to 1 because @@rowset was equal to 1 as a result of the last insert, and then resets the value of @@rowcount back to one).

Lets change the original script to substitute 3 inserts (each inserting one record) with a single insert inserting 3 records in one set. If your statement about SET never yields a rowcount is correct then we should see the final answer as 33333, but the result is actually going to be 31113 insead.

declare @result varchar (5);
create table #n (n int);

insert into #n
select
top 3 row_number() over (order by [object_id])
from sys.objects;

-- @@rowcount is equal to 3 after the insert, and @@rowcount is equal
--to 1 after the set below is executed, so set @result = ... does indeed
--sets the rowcount. The confusion comes from the fact that the statements
-- such as set ansi_nulls, quoted_identifier or whatever other set do not
-- have so-called rows affected so those set statements reset the @@rowcount
-- to 0, that is all. They still change the value of the @@rowcount though :)
set @result = cast (@@rowcount as varchar);

select @result = @result + cast (@@rowcount as varchar) from #n;
select @result + cast (@@rowcount as varchar);

The above yields 31113 not 33333

Oleg


Post #903234
Posted Wednesday, April 14, 2010 9:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:08 PM
Points: 1,186, Visits: 1,976
This is a nice example of why you SHOULD NOT test @@ROWCOUNT at the start of a trigger and exit if the value is zero. Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger or program unit (procedure, function). I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to when the trigger is fired.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #903240
Posted Wednesday, April 14, 2010 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 2,917, Visits: 2,528
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.

@@ROWCOUNT changes after each SQL call, which is why it needs to be immediately after any command which you want to check the rowcont of.

@@ROWCOUNT returns the specific number of rows either returned from a query or affected by a transaction. The value of @@ROWCOUNT is ALWAYS the value of the immediate preceeding SQL call. Thus, your example is selecting 3 rows and inserting them as a batch. The QOD is performing 3 seperate inserts.


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #903261
Posted Wednesday, April 14, 2010 9:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #903272
Posted Wednesday, April 14, 2010 10:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:50 PM
Points: 17,807, Visits: 15,727
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
Post #903335
Posted Wednesday, April 14, 2010 10:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:58 AM
Points: 1,142, Visits: 2,689
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
Post #903367
Posted Wednesday, April 14, 2010 12:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 2,387, Visits: 2,776
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #903446
Posted Wednesday, April 14, 2010 1:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:46 AM
Points: 3,059, Visits: 944
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.
Post #903510
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse