In Sql Server, if you try to count more than 2.1 billion rows or sum up values more than that, you will get this message: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.
Today I will show you the solution to this problem.
Below I am trying to count how many rows I have in my table.
The problem is that the COUNT function uses INT as its default datatype, but this table has over 4 billion rows so we get the overflow above.
What can we do? There is also a function called COUNT_BIG which uses BIGINT as its return datatype. Using this function allows us to count all of the rows in the table.
You can also get the overflow while summing up values more than 2.1 billion like below.
In this case you need to convert the reading field to BIGINT using the CONVERT function so you can sum large values. Doing that allows your query to run.
This link gives you more information about the BIG_COUNT function: https://docs.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql?view=sql-server-ver15
This link provides the size limits for INT, BIGINT, SMALLINT, and TINYINT. https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15