Tuesday, May 01, 2012

BIGINT - Upper limit - Overflow - SQL Server

BIGINT upper limit is 2^63-1 (9,223,372,036,854,775,807). For complete reference check out this MSDN article

Recently I was asked when we use INT data type and it reaches its limit what do we do? The following is the error message we would see when it reaches its upper limit.


Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Though there are multiple solutions, one of the option for us is to change the datatype to BIGINT.

The person who asked me wasn't satisfied with this answer. He was worried is this a permanent solution? Won't BIGINT also overflow / reach its limits sooner or later?

Obviously BIGINT would also reach its limit but it would take really LOTS of years + millions of transactions per second for it. Actually I wouldn't bother about it at all for the reasons explained below.

Let's take few examples and see how many years will it take for BIGINT to reach its upper limit in a table:

(A) Considering only positive numbers, Max limit of BIGINT = 9,223,372,036,854,775,807
(B) Number of Seconds in a year = 31,536,000

Assume there are 50,000 records inserted per second into the table. Then the number of years it would take to reach the BIGINT max limit is:

9,223,372,036,854,775,807 / 31,536,000 / 50,000 = 5,849,424 years

Similarly,
If we inserted 1 lakh records per second into the table then it would take 2,924,712 yrs
If we inserted 1 million (1000000) records per second into the table then it would take 292,471 yrs
If we inserted 10 million (10000000) records per second into the table then it would take 29,247 yrs
If we inserted 100 million records per second into the table then it would take 2,925 yrs
If we inserted 1000 million records per second into the table then it would take 292 yrs

By this we would have understood that it would take extremely lots of years to reach the max limit of BIGINT. May be end of world would be earlier than this :) Atleast I have not seen (or) heard of any application which has exceeded the BIGINT's max limit as of now. Please feel free to let me know if you have seen any.

By the way, if you are wondering how we calculated the number of seconds in a year.
It is just 365 days * 24 hours * 60 minutes * 60 seconds = 31,536,000 seconds.