Is it possible to run out of bigint values?

Question: I’ve been told that it’s possible to run out of values when using the bigint data type and that I should use GUIDs instead, regardless of the problems they can cause. Is this true?

Answer: This is similar to the question I answered last year on whether it’s possible to run out of virtual log file sequence numbers – where the answer is yes, but it would take 240 million years to do so.

Related: Is It Possible to Run Out of Log Sequence Numbers?

Similarly, yes, you can run out of bigints but it’s not practical that you will.

Bigint allows you to have +/- 2^63 (or +/- 9,223,372,036,854,775,808). Say for argument’s sake that you’re able to process 100 thousand pieces of data per second, and you assign an ever-increasing bigint value for each one. You’d have to be processing continuously for 2^63 / 100,000 / 3,600 hours – which works out to be 2.925 million years.

Now that’s just using the bigints – if you wanted to store them too, you’d run out of storage space before running out of numbers.

Doing a quick test of a heap with a single bigint identity column shows me that I can get 453 rows per 8KB data file page (don’t forget the record overhead, slot array overhead, and that the heap pages won’t be filled completely because of the way the free space caching and searching works). A terabyte of data would store roughly 61 billion rows.

At that rate, actually running out of bigints AND storing them would take roughly 150 thousand petabytes. This is clearly impractical – especially when you consider that simply storing a bigint is pretty pointless – you’d be storing a bigint and some other data too – probably doubling the storage necessary, at least.

So yes, while it is theoretically possibly to run out of bigint values, in reality it’s not going to happen.

Learn more: Performance Effects of Using GUIDs as Primary Keys

Discuss this Blog Entry 2

on Dec 23, 2011
As a mathematician might say, your answer is almost correct, but not sufficient. The calculation you show only yields the number of hours, not the number of years, which is, happily, close to 2.925 million. To get there, however, you must also diver by 24, then by 365.25, which equals 2,922,710 years and change. Your answer is close enough for government work. However, the conditions you assume are a bit optimistic given today's computational capabilities. First, there is no reason you would have to store the integers - records could conceivable be deleted as you go, saving only interesting ones. So your storage space argument is moot. Second, the rate of 100,000 transactions per second is too low. For example, in a Beowolf cluster with a distributed database I could easily process a billion records per second of, sensor values from, say, the Hubble space telescope. That brings us down to only 2.92 years. Calculated data sets, auch as those produced by protein folding computations, can proceed at even faster rates. So it's not inconceivable, even today, that you could experience big int exhaustion. The risk depends on your application.
on Dec 23, 2011
As a mathematician might say, your answer is almost correct, but not sufficient. The calculation you show only yields the number of hours, not the number of years, which is, happily, close to 2.925 million. To get there, however, you must also diver by 24, then by 365.25, which equals 2,922,710 years and change. Your answer is close enough for government work. However, the conditions you assume are a bit optimistic given today's computational capabilities. First, there is no reason you would have to store the integers - records could conceivable be deleted as you go, saving only interesting ones. So your storage space argument is moot. Second, the rate of 100,000 transactions per second is too low. For example, in a Beowolf cluster with a distributed database I could easily process a billion records per second of, sensor values from, say, the Hubble space telescope. That brings us down to only 2.92 years. Calculated data sets, auch as those produced by protein folding computations, can proceed at even faster rates. So it's not inconceivable, even today, that you could experience big int exhaustion. The risk depends on your application.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×