How BIG is too BIG for a BIGINT IDENTITY?

After writing a recent blog post on Are you wasting half the capacity of IDENTITY, I started thinking about how much is too big for an BIGINT IDENTITY.

If you don’t know about, you can get Are you wasting half the capacity of IDENTITY 9,223,372,036,854,775,807 IDENTITY values out of a BIGINT in SQL Server.  Which in easier terminology is 9 with roughly 18 digits after it, which works out to 9 quintillion (us) or 9 trillion for the rest of the world.  Translating again that is roughly 9 billion billion, which is a huge amount of IDENTITY values.

Now if you consier the article of Are you wasting half the capacity of IDENTITY you can get just about 18.5 quintillion rows out of an identity column.  I know as DBA’s and programmers we all like to plan big, but there are very few real world cases where you would need more than 18.5 quintillion rows out of an IDENTITY COLUMN.

So I ask the question, what are you doing where you need more than 18.5 quintillion different id’s in a table?  Perhaps its time to rethink the table design if it is getting that big.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!