Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

IDENTIY COLUMN Property behaviour Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 9:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 2,095, Visits: 3,146
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.



An int can hold a value up to 2,147,483,647, so the number of values left from 40M woud be:
2,107,483,647


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1367129
Posted Tuesday, October 2, 2012 9:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1367140
Posted Tuesday, October 2, 2012 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
Thank you both. There was an article somewhere referring to the limitations for identity column values with respect to replication. That is what sent me looking.
The chart answers my concern. We can't use negative numbers but the int will suffice for order and master order numbers.

Post #1367146
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse