Joel’s answer is correct, it is the upper limit of whatever datatype you use.
Here’s an example of two of them:
- int: 2^31-1 (2,147,483,647)
- bigint: 2^63-1 (9,223,372,036,854,775,807)
I have actually hit the limit at a job I worked at. The actual error is:
Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.
There are a couple fixes to this I can think of off the top of my head. Number 1 is probably very hard and not very likely, number 2 is easy, but will probably cause problems in your code base.
- If the identity column doesn’t matter to you (it’s not a Foreign Key, etc.) then you can just reseed the database and reset the identity column.
- Change your identity column to a bigger number. So for example if you’ve overflowed an int, change your identity column to a big int. Good luck overflowing that 🙂
There are probably other fixes, but there is no magic bullet easy one. I just hope this doesn’t happen in a table that is the center of a bunch of relationships, because if it does, you’re in for a lot of pain. It’s not a hard fix, just a tedious and long one.