Upper Limit for Autoincrement Primary Key in SQL Server

Upper limit for autoincrement primary key in SQL Server

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.

What to do if the auto-increment value reaches its limit?

Let's assume a table structure like:

CREATE TABLE `tbl` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

and INSERT queries like:

INSERT INTO tbl(id) VALUES (NULL);

In the real code there are also other columns in the table and they are also present in the INSERT query but we can safely ignore them because they don't bring any value to this specific issue.

When the value of column id reaches its maximum value no more rows can be inserted in the table using the query above. The next INSERT fails with the error:

SQL Error (167): Out of range value for column 'id'.

If there are gaps in the values of the id column then you can still insert rows that use values not present in the table but you have to specify the values for id in the INSERT query.

---

Anyway, if the type of your AUTO_INCREMENT column is BIGINT you don't have to worry.

Assuming the code inserts one million records each second (this is highly overrated, to not say impossible), there are enough values for the id column for the next half of million years. Or just 292,277 years if the column is not UNSIGNED.

---

I witnessed the behaviour on a live web server that was using INT(11) (and not UNSIGNED) as the AUTO_INCREMENTed PK for a table that records information about the visits of the web site. It failed in the middle of the night, after several years of running smoothly, when the visits number reached 2^31 (2 billions and something).

Changing the column type from INT to BIGINT is not a solution on a 2-billion records table (it takes ages to complete and when the system is live, there is never enough time). The solution was to create a new table with the same structure but with BIGINT for the PK column and an initial value for the AUTO_INCREMENT column and then switch the tables:

CREATE TABLE `tbl_new` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) AUTO_INCREMENT=2200000000;

RENAME TABLE `tbl` TO `tbl_old`, `tbl_new` TO `tbl`;

Can you set the max an autoincrement will reach

I'd use a CHECK constraint rather than a trigger but you can easily set the start and allowed range of values for a column.

Create a test table:

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test (
    ID INT IDENTITY(10,1) /* Start at 10, increment by 1 */
    , [Text] VARCHAR(100)
)
GO

Add constraint

ALTER TABLE #test
ADD CONSTRAINT CK_IDRange CHECK (ID >= 10 AND ID <= 20)
GO

Create some rows

INSERT INTO #test ([Text])
VALUES (NEWID())
GO 11

SELECT * FROM #test

Fail

INSERT INTO #test ([Text])
VALUES ('should fail')

primary keys, max limit

The limit is related to the datatype itself, not with the fact of being auto increment.If you are concerned about the maximum size, you can start the sequence below zero, and thus double the capacity, like this:

CREATE TABLE [MYTABLE](
[ID] [int] IDENTITY(-2147483648,1),
    (...)

While the ranges of values for different data type are like this:https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql

1. bigint -
   Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
   Storage: 8 Bytes
2. int - Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) Storage: 4 Bytes
3. smallint - Range -2^15 (-32,768) to 2^15-1 (32,767) Storage: 2 Bytes
4. tinyint - Range 0 to 255 Storage: 1 Bytes

What happens to the primary key Id when it goes over the limit?

You get an error if the identity would exceed the bounds of the datatype making the rest of your question moot. You can see this by

CREATE TABLE #T
(
id INT IDENTITY(2147483647,1)
)

INSERT INTO #T
DEFAULT VALUES

INSERT INTO #T
DEFAULT VALUES /*Arithmetic overflow error converting IDENTITY to data type int.*/

GO

SELECT * FROM #T

DROP TABLE #T

What happens when auto increment primary key in MySQL comes to maximum

The MySQL official documentation states that :

When the column reaches the upper limit of the data type, the next
attempt to generate a sequence number fails. Use the UNSIGNED
attribute if possible to allow a greater range.

And yes, you can switch the types of the auto_increment value. On this point, the documentation advises to use :

[...] the smallest integer data type for the AUTO_INCREMENT column
that is large enough to hold the maximum sequence value you will need.

Max Size of SQL Server Auto-Identity Field

An INT will take you up to 2,147,483,647.

A BIGINT will get you 9,223,372,036,854,775,807.

Maximum for autoincremental int primary key in SqlServer

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.

SQL Server: arbitrary auto-increment of primary key

UPDATE Thanks to Marting & Aron, I've found a work-around. Here's the official response from Microsoft:

In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

If you require the same identity generation semantics as previous versions of SQL Server there are two options available:

• Use trace flag 272 o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.

• Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx) o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.

Example:

CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE; 
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);

---

Related Topics