What happens when my integer IDENTITY runs out of scope?

You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of: 

TINYINT	0 – 255
SMALLINT	-32.768 – 32.767
INT	-2.147.483.648 – 2.147.483.647
BIGINT	-2^63 – 2^63-1

When you decide to use the DECIMAL datatype you have a potential range from -10^38 to 10^38-1. 


Now, what happens when an integer identity crosses the range.


Example


CREATE TABLE Int_Identity ( col1 INT IDENTITY(2147483647,1) )

GO

INSERT INTO  Int_Identity DEFAULT VALUES 

INSERT INTO  Int_Identity DEFAULT VALUES 

SELECT * FROM Int_Identity

DROP TABLE Int_Identity

(1 row(s) affected) 

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

The above script we have created a simple table with just one column and also created Identity property for this column. Now instead of adding 2 billion rows, we just seed value to maximum positive number for integer. Now the first row inserted and assigned that seed value. Now when we try to insert the second record, It failed showing above error.

Now the easiest solution is to alter the data type of the column to BIGINT, or maybe right on to DECIMAL(38,0).