@@ROWCOUNT – Get the Number of Rows Affected by the Last Statement in SQL Server

by

In SQL Server, you can use the @@ROWCOUNT system function to return the number of rows affected by the last T-SQL statement.

For example, if a query returns 4 rows, @@ROWCOUNT will return 4.

Example 1 – Selecting Data

Here’s a basic example to demonstrate how it works.

SELECT * FROM Dogs;
SELECT @@ROWCOUNT;

Result:

+---------+-----------+-----------+
| DogId   | DogName   | GoodDog   |
|---------+-----------+-----------|
| 1       | Fetch     | 0         |
| 2       | Fluffy    | 0         |
| 3       | Wag       | 0         |
+---------+-----------+-----------+
(3 rows affected)
+--------------------+
| (No column name)   |
|--------------------|
| 3                  |
+--------------------+
(1 row affected)

In this case, my SELECT statement returned 3 rows, and so @@ROWCOUNT returned 3.

Example 2 – Updating Data

Here’s an example of using @@ROWCOUNT with an UPDATE statement to test whether or not any rows were updated.

UPDATE Dogs 
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
    PRINT 'Your dog will be rewarded accordingly';
ELSE
    PRINT 'A dog outside the system cannot be a good dog'
GO

Result:

(0 rows affected)
A dog outside the system cannot be a good dog

In this case, no rows were updated because the DogId didn’t exist in the table. We were able to use @@ROWCOUNT with an IF statement to return an appropriate message to the user.

Here it is again, but this time the dog does exist.

UPDATE Dogs 
SET GoodDog = 1
WHERE DogId = 1
IF @@ROWCOUNT > 0
    PRINT 'Your dog will be rewarded accordingly';
ELSE
    PRINT 'A dog outside the system cannot be a good dog'
GO

Result:

(1 row affected)
Your dog will be rewarded accordingly

Extremely Large Dataset?

If you think the amount of rows affected by a statement is going to be more than 2 billion, use ROWCOUNT_BIG() instead.

You can use it in the same way that @@ROWCOUNT is used.

UPDATE Dogs 
SET GoodDog = 1
WHERE DogId = 4
IF ROWCOUNT_BIG() > 0
    PRINT 'Your dog will be rewarded accordingly';
ELSE
    PRINT 'A dog outside the system cannot be a good dog'
GO

Result:

(0 rows affected)
A dog outside the system cannot be a good dog

When @@ROWCOUNT is Reset

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the @@ROWCOUNT value to 0.

Running SELECT @@ROWCOUNT by itself will also return 0.