November 27, 2012 Leave a Comment
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.