Arithmetic overflow error converting IDENTITY to data type int

If we have an IDENTITY column and if our insert statement is trying to exceed the maximum value of INTEGER then it would throw this error.

To know the range for TinyInt, SmallInt, INT, BIGINT check out this MSDN link

Lets reproduce the error for an TINYINT column

CREATE TABLE dbo.tblIdentityTest
(
Sno TINYINT IDENTITY(250,1) --Max limit of TinyInt is 255
,Firstname VARCHAR(20)
)
GO

--These records would get inserted
INSERT INTO dbo.tblIdentityTest VALUES ('250')
INSERT INTO dbo.tblIdentityTest VALUES ('251')
INSERT INTO dbo.tblIdentityTest VALUES ('252')
INSERT INTO dbo.tblIdentityTest VALUES ('253')
INSERT INTO dbo.tblIdentityTest VALUES ('254')
INSERT INTO dbo.tblIdentityTest VALUES ('255')
GO


SELECT * FROM dbo.tblIdentityTest
GO

--As TINYINT has already reached it max limit any new insertion would fail
INSERT INTO dbo.tblIdentityTest VALUES ('This would fail')
GO

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

--Cleanup
DROP TABLE dbo.tblIdentityTest
GO

Lets reproduce the error for INTEGER column:

CREATE TABLE dbo.tblIdentityTest2
(
Sno INT IDENTITY(2147483645,1) --Max limit of INT is 2,147,483,647
,Firstname VARCHAR(20)
)
GO

--These records would get inserted
INSERT INTO dbo.tblIdentityTest2 VALUES ('Alpha')
INSERT INTO dbo.tblIdentityTest2 VALUES ('Beta')
INSERT INTO dbo.tblIdentityTest2 VALUES ('Gamma')
GO


SELECT * FROM dbo.tblIdentityTest2
GO


--As INT has already reached it max limit any new insertion would fail
INSERT INTO dbo.tblIdentityTest2 VALUES ('This would fail')
GO

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

--Cleanup
DROP TABLE dbo.tblIdentityTest2

In both of the above cases for demonstration purpose we had seeded the value nearest to its maximum limit of that data type. Though we know that we have very less rows in the table still SQL Server would throw this Arithmetic overflow error. This would show us that SQL Server doesn't automatically try to fill the existing gaps in the Identity sequence!

Normally based on the requirements we have to choose an appropriate data type to store our data. For the above issues one of the easiest way to solve this issue is to change the data type as BIGINT.

ALTER TABLE dbo.tblIdentityTest2 ALTER COLUMN Sno BIGINT

GO

This way now the table can store upto maximum of  2^63-1 (9,223,372,036,854,775,807) records in it. Actually, if we need, we can start the sequence from the minimum value of BIGINT (-9223372036854775808).

CREATE TABLE dbo.tblIdentityTest3
(
Sno INT IDENTITY(-9223372036854775808,1) 
,Firstname VARCHAR(20)
)
GO

So this table can now store data from -9223372036854775808 to 0 and then from 0 to 9223372036854775807. It can store huge amount of rows before it can run out of numbers.

IDENTITY column can also be of data type NUMERIC or DECIMAL. The only restriction there is its scale can ONLY be 0. As decimal data type cannot have more than 38 precision the value it can store is -99999999999999999999999999999999999999 to 99999999999999999999999999999999999999

CREATE TABLE dbo.tblIdentityTest3
(
Sno DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999,1)
,Firstname VARCHAR(20)
)
GO

Related posts: $IDENTITY in SQL Server