CODEWITHSUNDEEP
sqlsql-serverauto-increment
Dema
Dema

Reputation: 6887

Upper limit for autoincrement primary key in SQL Server

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?

Upvotes: 36

Views: 30929

Answers (5)

CubanX
CubanX

Reputation: 5252

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.

Upvotes: 39

Nick Wright
Nick Wright

Reputation:

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.

Upvotes: 1

David M
David M

Reputation:

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.

Upvotes: 2

Fred
Fred

Reputation: 5006

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415971

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.

Upvotes: 4

Related Questions