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?

Answer 1

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

Answer 2

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