In SQL Server, the COUNT_BIG() function and the COUNT() do essentially the same thing: return the number of items found in a group. Basically, you can use these functions to find out how many rows are in a table or result set.
In many cases, you’ll be able to choose whichever one you prefer. However, there’s a difference between these two functions that might dictate that you to use one over the other.
The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its result as a bigint.
In other words, you’ll need to use COUNT_BIG() if you expect its results to be larger than 2,147,483,647 (i.e. if the query returns more than 2,147,483,647 rows).
Example 1 – When COUNT() is OK
Here’s a basic example showing a scenario where both COUNT() and COUNT_BIG() can be used:
USE WideWorldImportersDW; SELECT COUNT(*) AS 'COUNT', COUNT_BIG(*) AS 'COUNT_BIG' FROM Fact.[Order];
Result:
+---------+-------------+ | COUNT | COUNT_BIG | |---------+-------------| | 231412 | 231412 | +---------+-------------+
We can see that there are 231412 rows in the Fact.[Order] table.
In this case, both functions can handle it, because the row count is small enough to be stored in an int as well as a bigint.
However, if the result was so big that an int couldn’t store it, then we’d only be able to use COUNT_BIG().
Example 2 – When COUNT_BIG() is Required
Here’s an example of where you’d need to use COUNT_BIG().
SELECT COUNT_BIG(*) AS 'Row Count' FROM ReallyBigTable;
Result:
+-----------------+ | Row Count | |-----------------| | 9147483648 | +-----------------+
In this case, the row count is so big that an int wouldn’t be able to handle it. Fortunately we can use COUNT_BIG(), because it returns its result as a bigint.
Checking the Data Type of Both Functions
When we look at the previous examples, we can’t actually see the data type name. We can only assume that COUNT() returns its results as an int and COUNT_BIG() uses bigint because that’s what the Microsoft documentation says (although we do know that the second example couldn’t be an int because the value is too big for an int).
We can use the sp_describe_first_result_set stored procedure to check the return data type each of these functions.
Check the Data Type for COUNT()
EXEC sp_describe_first_result_set N'SELECT COUNT(*) FROM Fact.[Order]', null, 0;
Result (using vertical output):
is_hidden | 0 column_ordinal | 1 name | NULL is_nullable | 1 system_type_id | 56 system_type_name | int max_length | 4 precision | 10 scale | 0 collation_name | NULL user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | NULL source_schema | NULL source_table | NULL source_column | NULL is_identity_column | 0 is_part_of_unique_key | NULL is_updateable | 0 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 38 tds_length | 4 tds_collation_id | NULL tds_collation_sort_id | NULL
Yes, there’s a lot of info we don’t need, but if you look at the system_type_name column, you’ll see that its value is int. This tells us that our query returned its results as an int, as expected. You can also see that the max_length and precision values are consistent with the int data type.
Check the Data Type for COUNT_BIG()
For this example, all we need to do is replace COUNT(*) with COUNT_BIG(*):
EXEC sp_describe_first_result_set N'SELECT COUNT_BIG(*) FROM Fact.[Order]', null, 0;
Result (using vertical output):
is_hidden | 0 column_ordinal | 1 name | NULL is_nullable | 1 system_type_id | 127 system_type_name | bigint max_length | 8 precision | 19 scale | 0 collation_name | NULL user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | NULL source_schema | NULL source_table | NULL source_column | NULL is_identity_column | 0 is_part_of_unique_key | NULL is_updateable | 0 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 38 tds_length | 8 tds_collation_id | NULL tds_collation_sort_id | NULL
This time we can see that system_type_name is bigint. This tells us that our COUNT_BIG() query returned its results as a bigint, as expected. The max_length and precision values are also consistent with the bigint data type.
By the way, a quicker way of doing the above is to combine both functions into query when calling the stored procedure.
Like this:
EXEC sp_describe_first_result_set N'SELECT COUNT(*), COUNT_BIG(*) FROM Fact.[Order]', null, 0;
That will output two rows, one for each function in the SELECT statement.