Upper limit for autoincrement primary key in SQL S

问题:

What is the upper limit for an autoincrement primary key in SQL Server? What happens when an SQL Server autoincrement primary key reaches its upper limit?

回答1:

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.

1. 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.
2. 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.

回答2:

It depends on the datatype. If you use bigint, you're unlikely to ever overflow. Even a normal int gives you a couple billion rows. I've never overflowed, so I can't tell you what happens if you do.

回答3:

I'll tell you what happens.... my data stopped inserting into that specific table. The database still works but I found data missing and inconsistent. With a little research, I found the error table, then ran a manual insert. The error is the same as above.

Had to change the column to BIGINT. On a 26GB database on a somewhat slow server, took about 30 minutes. On the archive version of the database (150GB or so) it took quite a bit longer.

Fortunately, not too many relationships for this table so the pain was pretty slight.

回答4:

Data types descriptions:

BIGINT    Integer data from -2^63 through 2^63 - 1

INT       Integer data from -2^31 through 2^31 - 1

SMALLINT  Integer data from -2^15 through 2^15 - 1

TINYINT   Integer data from 0     through 255

When you reach the upper limit the autoincrement goes to the lower limit.

回答5:

DBCC CHECKIDENT (SomeTable, RESEED, 1)

This resets the identity to 1 on table 'SomeTable'

Not sure if this is the best way to do this.