Msg 8115, Level 16 Arithmetic overflow

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s