Regenerate IDENTITY column values on SQL Server and Azure SQL with sp_identity_rebirth
The sp_identity_rebirth stored procedure addresses the SQL Server and Azure SQL error 8115!
Introduction
Recently, I faced the SQL error 8115:
Arithmetic overflow error converting IDENTITY to data type int.This article describes an option to regenerate IDENTITY column values in SQL Server and Azure SQL, ensuring referential integrity and preserving related objects like constraints, indexes, and triggers. This solution prevents data overflow errors and maintains consistency without renaming or losing linked objects. Below are the error variations that occur when an overflow happens on IDENTITY columns.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type tinyint.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type smallint.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type int.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type bigint.Case history
An overflow error occurs whenever we try to insert a value into a column that exceeds the data type’s limit. In the case I followed, it was an integer column with the IDENTITY(1, 1) property, automatically incremented by SQL Server or Azure SQL with each data insertion. Overflow can occur on integer columns (as in this case) but also on tinyint, smallint, and bigint columns. The table in question contained about 600,000 records, but the current value of the IDENTITY column exceeded the integer data type limit of 2,147,483,647. The data type limits are documented here: Transact-SQL int, bigint, smallint, and tinyint. The data type limit was reached due to multiple inserts and deletions in the problematic table. In the reported case, the IDENTITY column was also the primary key, referenced by a foreign key defined on a detail table.
Resolution steps
One possible solution is to change the data type of the IDENTITY column. For example, if it is smallint, change it to integer, or if it is already an integer, as in this case, change it to bigint. However, changing the data type of an IDENTITY column involves several potential issues and considerations that must be carefully evaluated. If the IDENTITY column is referenced by foreign keys in other tables, as in this case, you will also need to update the data type of those columns to ensure compatibility. You will need to update application code, queries, stored procedures, and reports that expect the IDENTITY column to be of a certain data type. Any integration with external systems that uses the IDENTITY column will need to be updated to reflect the new data type, which could involve significant changes in the integrated systems.
Additionally, changing the data type from integer to bigint increases the amount of storage space required for each value. This can affect performance, especially in very large tables. If these issues are significant, an alternative solution is needed.
An alternative solution is to compact, when possible, the values of the IDENTITY column without renaming the table and without losing connected objects such as constraints, indexes, statistics, triggers, etc., whose management would significantly complicate the solution.
Because the table in question contained about 600,000 records, I chose to explore the solution that involves compacting the values. I adopted an approach that uses a temporary column to store the current values in the IDENTITY column, regenerate new values, and update the linked tables. It is important to note that you cannot directly update an IDENTITY column, even with IDENTITY_INSERT set to ON. From this study, the stored procedure sp_identity_rebirth was born! It uses a multi-phase strategy to regenerate the values of an IDENTITY column, maintaining referential integrity and minimizing the risks of data loss.
How the stored procedure sp_identity_rebirth works
The procedure includes the following steps:
Input parameter validation:
- Verifies that the schema, table, and IDENTITY column names are not empty
- Check if the IDENTITY column exists in the specified table
Primary key verification:
- Determines if the IDENTITY column is the primary key. If not, the procedure stops
Preparation for IDENTITY regeneration:
- Collects necessary SQL commands in a temporary table for sequential execution
Foreign key management:
- Identifies and removes foreign keys referencing the primary key to avoid conflicts
Table backup and manipulation:
- Adds a temporary column to store current IDENTITY values
- Creates a backup of the original table
- Truncates the original table to reset IDENTITY values
Data re-insertion and foreign key creation:
- Re-inserts data from the backup table to the original table, excluding the IDENTITY column
- Updates foreign key references to reflect the new IDENTITY values
- Recreates previously removed foreign keys
Transaction and error handling:
- Starts an explicit transaction if none exists
- Rollback the transaction and raises an error in case of failure
- Commit the transaction if all commands execute successfully
The powerful stored procedure, sp_identity_rebirth, is now available for download! Head over to my GitHub repository to get it now:
Outcome
By utilizing the sp_identity_rebirth stored procedure, you can successfully manage and regenerate IDENTITY column values in SQL Server and Azure SQL tables while maintaining referential integrity and preserving related objects such as constraints, indexes, and triggers. This approach effectively prevents data overflow errors and ensures data consistency without the need to rename tables or lose linked objects. Overall, integrating sp_identity_rebirth into your database maintenance strategy will help address issues related to IDENTITY column overflows and maintain the integrity of your database schema and relationships.
Enjoy!
