SQL Server 2008: what happened if identity oversteps a maximal value of int?

Question

Imagine we have a table:

create table MYTABLE (
 id int IDENTITY(1,1)
,name varchar(10)
)

We have to insert a lot of rows into the table.

Does anybody know what will happen when a generated identity value oversteps a maximal integer value (2^32-1)?

score 4 · Accepted Answer · edited Apr 13 '17 at 12:14

4

You will get the following error:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
You won't be able to insert more records, until you:
- delete some and reset the identity column using the DBCC CHECKIDENT command,
  or
- change column's data type to bigint (suggested by Gabriel Guimarães).

edited Apr 13 '17 at 12:14

Community

answered Jan 24 '11 at 10:51

Marek Grzenkowicz

2

or alter the column to bigint? – Gabriel Guimarães Jan 25 '11 at 11:50
1

or decimal (20,0)? – Timofey Feb 01 '11 at 14:04
You can also reseed the identity to a large, negative number, in a pinch. – Izzy Aug 31 '18 at 22:27

1 Answers1