Skip to main content

Posts

Showing posts from May, 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 o