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 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

  • 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 #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

  • 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