Identity Field

Question

Identity Field

Martin D. Cymerman

SSCrazy

Points: 2079
More actions
August 21, 2007 at 9:02 am

#121249

Is there an upper limtit to an identity field? If so, is there a way around it?
Thanks,
CY

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

It is limited by the upper limit of the datatype used. So the solution is to change the datatype, r even better, choose the correct one from the get go.

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 2

Our web guy is using an int...and was just wondering if down the line he might encounter problems.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Starting from 1, that's over 2 Billion rows inserted. If you expect way more than that, then I'd suggest using bigint.

Steve Jones - SSC Editor SSC Guru Points: 738860 More actions · Answer 4

I've run out of INTs in some situations. Like SMS, constant scan type of stuff.

Eventually it's an issue. BigINT gives you more space. If you run out, need to move to GUIDs, varchar, etc.

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 5

Thanks for the input...it's nice to prepare for disasters

Marty

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Run out of bigint???

Any case scenario you can present?

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

In the case you do run out of ints, moving to bigint would require you to rebuild the table. Generally this would involve creating a temp table, moving the data while switching identity insert on, setting the new identity seed, deleting the old table, renaming the new one, while taking care of the dependent objects. Not too much though, but a bit more than just altering a table column. Careful planning can however save this future headache.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Heh! Yeah, I'd like to see that, as well

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)