IDENTIY COLUMN Property behaviour

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » IDENTIY COLUMN Property behaviour

13 posts, Page 2 of 2

««1 2

IDENTIY COLUMN Property behaviour

Rate Topic

Display Mode

Topic Options

Author

Message

ScottPletcher

Posted Tuesday, October 02, 2012 9:38 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1367129

GSquared

Posted Tuesday, October 02, 2012 9:55 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Ellen-477471 (10/2/2012)

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

Ellen-477471

Posted Tuesday, October 02, 2012 10:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 15, 2013 4:35 PM
Points: 113, Visits: 290

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 »

13 posts, Page 2 of 2

««1 2

Permissions