CODEWITHSUNDEEP
sql-server
Lieven Cardoen
Lieven Cardoen

Reputation: 25959

Maximum for autoincremental int primary key in SqlServer

Is there a maximum? Will SQL Server start throwing SqlExceptions when maximum is reached? How do you solve it? (archiving?)

From the SQL Server resources I can see that the maximum is 2,147,483,647. I'm far from that, but I was just curious.

Upvotes: 9

Views: 24340

Answers (5)

vahid basirat
vahid basirat

Reputation: 455

If you insist on using for example INT data type for your primary key and you have reached to the end of capacity of this data type, you have to do the following:

  1. Delete older data. For example, delete from TABLENAME where pk < 1000000 Of course if your data is important, before deleting, you have to move them into an archive table. That way, the range from 1 to 1000000 will become free and you can use it. Suppose that your primary key is dynamically inserted. That way you have to RESEED your primary key.

  2. Run this query on your table:

    DBCC CHECKIDENT(TableName, RESEED,0)

That way, your record will be inserted by primary key from 1.

But consider that if your pk reach to 1000001, you will encounter an error because this primary key exists in your table. In other words, you always have to consider your primary key.

Upvotes: 1

Ori Samara
Ori Samara

Reputation: 71

Note that if you truncate the table, you are essentially resetting the ID. May be useful in cases where you cannot drop and recreate the table.

Upvotes: 1

sergiom
sergiom

Reputation: 4887

you can see the error using this small example

use tempdb;

if OBJECT_ID('dbo.test', 'U') is not null drop table dbo.test

create table test
( id int identity not null,
  dummy int not null )
go

SET IDENTITY_INSERT dbo.test ON

insert into test(id, dummy) values(2147483647, 1)

SET IDENTITY_INSERT dbo.test OFF

insert into test(dummy) values(1)

the error:

(1 row(s) affected)
Msg 8115, Level 16, State 1, Line 8
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300609

The maximum for an int is indeed 2,147,483,647.

If you attempt to go past the maximum size, you will receive this error:

Msg 8115, Level 16, State 1, Line 2 
Arithmetic overflow error converting IDENTITY to data type int. 
Arithmetic overflow occurred.

If that is not big enough, use a bigint (9,223,372,036,854,775,807)

Upvotes: 12

J&#248;rn Schou-Rode
J&#248;rn Schou-Rode

Reputation: 38336

The maximum is defined by the datatype, not by the IDENTITY modifier. For an INT column, you already figured out the maximum. If you need a data type with a greater range, BIGINT is the obvious choice, and it can very well be marked IDENTITY.

  • TINYINT: 0 through 255
  • SMALLINT: -32768 through 32767
  • INT: -2147483648 through 2147483647
  • BIGINT: -9223372036854775808 through 9223372036854775807

I would expect an error to be raised when trying to insert rows after hitting the maximum, as the IDENTITY implementation would still try incrementing with each insert.

While it would certainly be possible to archive rows to some other data store and start reusing identifiers (DBCC CHECKIDENT (jobs, RESEED, 0) will reset the counter), it is not something that SQL Server offers out-of-the-box. You will have to implement the logic for this yourself, and further you will have to consider what kind of trouble reusing identifiers might bring to your application. For a web application, for instance, will old URLs suddently point to new documents or return 404 errors?

Upvotes: 5

Related Questions