SQL Server - is using @@ROWCOUNT safe in multithreaded applications?

Question

I am using SQL Server 2008.

I have a table A which accepts many insert/update in one seconds. After insert, update I want to get the number of rows affected.

INSERT INTO A (ID) VALUES (1)
IF @@ROWCOUNT = 0
    PRINT 'NO ROWS AFFECTED'

While query is being executed, the same query may be called again by application. So what happens if the current execution is after INSERT but before IF block at that moment.

Do you think @@ROWCOUNT may give wrong result for that reason?

Or is it always safe in its context?

score 23 · Accepted Answer · answered Jan 22 '12 at 10:51

Yes - its safe. It always refers the previous operation in current query

BUT

if you want to know the number of rows affected, save it to variable first, because after IF statement the count @@ROWCOUNT resets

INSERT INTO A (ID) VALUES (1)
DECLARE @rc INT = @@ROWCOUNT
IF @rc = 0
    PRINT 'NO ROWS AFFECTED'
ELSE
  SELECT @rc AS RowsAffected

score 8 · Answer 2 · answered Jan 22 '12 at 10:51

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope. The full rules are here on MSDN (cursors, DML, EXECUTE etc)

To use it in subsequent statements, you need to store it in a local variable.

Mitch Wheat · Answer 3 · 2014-10-21T23:19:54.360

1

You must preserve the @@ROWCOUNT value in a local variable, otherwise after the IF statement its value will reset to zero:

SET @rowCount = @@ROWCOUNT

IF @rowCount = 0 
   PRINT 'NO ROWS AFFECTED'

Other than that, yes, it is safe.

edited Oct 21 '14 at 23:19

answered Jan 22 '12 at 10:53

Mitch Wheat

295,962
43
465
541

I just tested this against a MS SQL 2008 R2 DB and the `IF` check against `@@ROWCOUNT` worked without a problem. I can understand that it could be a good practice to always locally store `@@ROWCOUNT` before doing anything with it. But why did you say that you **must** first store it in a local variable? Do you just mean that this is a good practice or is it actually unreliable to directly check the value of `@@ROWCOUNT`? – Sam Nov 21 '12 at 23:23
You must store it's value before accessing it. – Mitch Wheat Nov 21 '12 at 23:26
1

I did a bit of searching to find any references that indicate that this is necessary, but I didn't find any. Is there a reference or source that you can provide to demonstrate that it's necessary to do this? – Sam Nov 29 '12 at 06:44
Indeed, the examples for all SQL versions 2005-2012 on MSDN* specifically do not store @@ROWCOUNT before referencing in an IF statement. I'm interested in any source that proves MSDN is incorrect. * http://technet.microsoft.com/en-us/library/ms187316.aspx – Griffin Dec 27 '13 at 17:03

score 0 · Answer 4 · answered Oct 24 '15 at 09:51

Short answer: Yes.

However it worth to see the question in a perspective, for the deeper understanding why the answer yes is so natural without doubt.

SQL Server is prepared to handle concurrent access correctly by its nature, regardless if the client application is multithreaded or not. Unless this attribute SQL Server would be useless in any multiuser scenario. From point of view of the server it does not matter if the concurrent access caused by one multithreaded application, or two applications which are currently using the server concurrently by multiple users.

Regarding this point the @@rowcount is only the top of the iceberg, there are much more and deeper functionality what must be handled correctly when concurrent access is in the picture.

The most practical part of this area is transaction management and transaction isolation.

SQL Server - is using @@ROWCOUNT safe in multithreaded applications?

4 Answers4

Linked