Skip to main content

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

Comments

I think is the most common error which people usually have which is Identity data but you well describe here.

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul