Ellen-477471 (10/2/2012)
Is there a chart somewhere that shows the maximum number of identity values for the data type used when creating an identity field in a table?I've searched for this in the BOL but have not had any luck.
We had someone design tables in a database to use the an identity column of datatype int for the orders and line item sequence numbers.
If the seed value is 40,000,000 for orders with an increment of 1, how many orders can be created before the field has reached maximum value?
Thank you.
It depends on the datatype of the column. Min and max values here: http://msdn.microsoft.com/en-us/library/ms187745.aspx
If you need a truly huge number of identities in a table, and don't want to go with BigInt because of the storage amount, keep in mind that you can seed an Int Identity column to start at -2,147,483,648, increment by 1, and pretty much double the number of available IDs (as compared to starting at 1, which is the default). That allows for over 4-billion (US billion) rows with unique ID values in that table. Not quite enough for every person on the planet, but still pretty vast.
BigInt, which takes twice the storage space as Int, can go from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is enough for over 18-pentillion entries. About 2.6-billion rows per person on the planet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon