@@ROWCOUNT vs ROWCOUNT_BIG() in SQL Server

by

SQL Server provides us with both a @@ROWCOUNT and a ROWCOUNT_BIG() function. You may be wondering what the difference is between these two functions?

Let’s find out.

The Difference

The difference between @@ROWCOUNT and ROWCOUNT_BIG() is in the return value:

  • @@ROWCOUNT returns an INT
  • ROWCOUNT_BIG() returns a BIGINT

That’s basically it.

So if you expect the result to be larger than 2 billion (2,147,483,647 to be exact), use ROWCOUNT_BIG().

If you don’t expect the result to be larger than 2 billion, then the @@ROWCOUNT function will suffice.

Example

Here’s an example of running ROWCOUNT_BIG() and @@ROWCOUNT side by side:

SELECT CatName FROM Cats;
SELECT 
    @@ROWCOUNT AS [@@ROWCOUNT],
    ROWCOUNT_BIG() AS [ROWCOUNT_BIG()];

Result:

CatName
-------
Meow   
Fluffy 
Scratch
Purr   
Furball
Scarcat
Rolly  

7 row(s) returned

@@ROWCOUNT  ROWCOUNT_BIG()
----------  --------------
7           7             

1 row(s) returned

We can see that both functions return the same number. The difference (although we can’t see it) is that one of them is an INT and the other is a BIGINT.

To verify the return data type of these two functions, we can pass our query to the sys.dm_exec_describe_first_result_set() function:

SELECT 
    name,
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'SELECT 
    @@ROWCOUNT AS [@@ROWCOUNT],
    ROWCOUNT_BIG() AS [ROWCOUNT_BIG()];', 
    null, 
    0);

Result:

name            user_type_name  system_type_name  max_length  precision  scale
--------------  --------------  ----------------  ----------  ---------  -----
@@ROWCOUNT      null            int               4           10         0    
ROWCOUNT_BIG()  null            bigint            8           19         0

This confirms that @@ROWCOUNT returns an INT and ROWCOUNT_BIG() returns a BIGINT.