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;
Optimizing ETL Workflows: A Guide to Azure Integration and Authentication with Batch and Storage
Unlock the Power of Azure: Transform Your ETL Pipelines Dive into the world of data transformation and discover how to build a solid foundation for your ETL pipelines with Azure’s powerful trio: Data Factory, Batch, and Storage. Learn to navigate the complexities of data authentication and harness the full potential of Synapse Pipeline for seamless integration and advanced data processing. Ready to revolutionize your data strategy? This guide is your key to mastering Azure’s services and optimizing your workflows.
