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

It works exactly the same as @@ROWCOUNT, except that ROWCOUNT_BIG() returns its result as a bigint.

Therefore, ROWCOUNT_BIG() is more suited to result sets that return a large number of rows (2 billion or more).

This is because @@ROWCOUNT returns its result as an int, which has an upper limit of just over 2 billion (2,147,483,647 to be exact).

Other than that, it can be used in exactly the same way as @@ROWCOUNT. So if you’re working with seriously large result sets, use ROWCOUNT_BIG() instead of @@ROWCOUNT.

Example

Here’s an example to demonstrate how ROWCOUNT_BIG() works.

SELECT ArtistId, ArtistName 
FROM Artists;
SELECT ROWCOUNT_BIG();

Result:

+------------+------------------------+
| ArtistId   | ArtistName             |
|------------+------------------------|
| 1          | Iron Maiden            |
| 2          | AC/DC                  |
| 3          | Allan Holdsworth       |
| 4          | Buddy Rich             |
| 5          | Devin Townsend         |
| 6          | Jim Reeves             |
| 7          | Tom Jones              |
| 8          | Maroon 5               |
| 9          | The Script             |
| 10         | Lit                    |
| 11         | Black Sabbath          |
| 12         | Michael Learns to Rock |
| 13         | Carabao                |
| 14         | Karnivool              |
| 15         | Birds of Tokyo         |
| 16         | Bodyjar                |
+------------+------------------------+
(16 rows affected)
+--------------------+
| (No column name)   |
|--------------------|
| 16                 |
+--------------------+
(1 row affected)