DaveWentzel.com            All Things Data

April 2013

How Big is a BIGINT?

8 bytes.  Who cares?

Like many shops, we use a BIGINT datatype as a sequence for Id's in our tables.  We generate lots and lots of these every second.  There's now a requirement where we may have to generate hundreds of thousands every few seconds.  People started sweating.  "Are we sure that we won't overflow a BIGINT after a few years if we are creating this many Identifiers so quickly?"  

Let's do the math.  

How many Identifiers can I create EVERY SECOND, before I will run out of Identifiers in 100 years?  

2.9 million identifiers can be created per second for the next 100 years before we overflow a BIGINT.  

And that's assuming we ONLY use the positive side of ZERO.  We didn't even calculate the negative Identifiers we could create.  Effectively this doubles the available Identifiers we have.  

In my mind, BIGINT really is a BIG datatype.  

sys.dm_db_index_usage_stats

I've written in the past about The Problems with the Missing Index DMVs.  I'm not opposed to using them, I'm just concerned people will use them INSTEAD OF doing proper analysis.  In that post I listed some gotchas.  

Tags: