Lesson Learned #528: Arithmetic overflow error converting IDENTITY to data type int
Some days ago, we were working on a service request where our customer was experiencing an issue with their application while reading a CSV file from Azure Blob Storage and writing the data into an Azure SQL Database table. They discovered that thousands of rows were being skipped. While analyzing the issue, we identified the following error message: Arithmetic overflow error converting IDENTITY to data type int. After a investigation, we found the root cause: The target table in production had an INT-typed IDENTITY column. The identity value had reached the maximum value of an INT (2,147,483,647). The application was configured with SET IDENTITY_INSERT ON, so it was trying to explicitly insert identity values from the CSV file. When it attempted to insert a value above the INT limit, it failed with an arithmetic overflow. How to reproduce the issue in SQL: CREATE TABLE dbo.TestDemo ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(100) ); -- Set the identity value to the maximum possible value for INT -- Only for testing... DBCC CHECKIDENT ('dbo.TestDemo', RESEED, 2147483646); SET IDENTITY_INSERT dbo.TestDemo ON; INSERT INTO dbo.TestDemo (ID, Name) VALUES (2147483647, 'Last Row'); INSERT INTO dbo.TestDemo (ID, Name) VALUES (2147483648, 'Overflow'); SET IDENTITY_INSERT dbo.TestDemo OFF;
