UGISS User Group Italiano SQL Server
Recentemente mi sono imbattuto nell’errore SQL 8115:
Arithmetic overflow error converting IDENTITY to data type int.Un errore di overflow su una colonna IDENTITY si verifica ogni volta che proviamo a inserire in una colonna un valore superiore al limite previsto per il tipo di dato. Nel caso che ho seguito si trattava di una colonna di tipo integer con proprietà IDENTITY(1, 1), valorizzata automaticamente da SQL Server ad ogni inserimento di dati. L’overflow può avvenire su colonne di tipo integer (come in questo caso) ma anche su colonne di tipo tinyint, smallint e bigint.
Di seguito sono riportate le variazioni di errore quando l’overflow avviene su colonne IDENTITY.
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.
La tabella in questione conteneva circa 600.000 record ma il valore corrente della colonna IDENTITY aveva superato il limite previsto per il tipo di dato integer ovvero 2.147.483.647 come documentato qui: Transact-SQL int, bigint, smallint, and tinyint.
Si è raggiunto il limite del tipo di dato a causa di molteplici inserimenti e cancellazioni nella tabella oggetto del problema. Nel caso segnalato la colonna IDENTITY era anche PRIMARY KEY referenziata da una FOREIGN KEY definita su una tabella di dettaglio.
Una possibile soluzione è modificare il tipo di dato della colonna IDENTITY. Ad esempio, se è smallint, cambialo in integer, oppure, se è già integer, come in questo caso, cambialo in bigint; tuttavia, cambiare il tipo di dato di una colonna IDENTITY comporta diversi potenziali problemi e considerazioni che è importante valutare attentamente. Se la colonna IDENTITY è referenziata da chiavi esterne in altre tabelle, come in questo caso, si dovrà aggiornare anche il tipo di dato di queste colonne per garantire la compatibilità; dovremo aggiornare il codice applicativo, query, stored procedure, e report che si aspettano che la colonna IDENTITY sia di un certo tipo di dato. Qualsiasi integrazione con sistemi esterni che utilizza la colonna IDENTITY dovrà essere aggiornata per riflettere il nuovo tipo di dato, il che potrebbe comportare modifiche significative nei sistemi integrati. In aggiunta, cambiare il tipo di dato da integer a bigint aumenta la quantità di spazio di archiviazione necessaria per ciascun valore. Questo può influire sulle prestazioni, soprattutto in tabelle molto grandi. Se questi problemi sono significativi, è necessario trovare una soluzione alternativa.
Una soluzione alternativa è compattare, quando possibile, i valori della colonna IDENTITY senza però rinominare la tabella e senza perdere oggetti collegati come vincoli, indici, statistiche, trigger, etc. la cui gestione complica notevolmente la soluzione.
Considerato che la tabella in questione conteneva circa 600.000 record, ho scelto di approfondire la soluzione che prevede la compattazione dei valori.
Ho adottato l’approccio che prevede l’utilizzo di una colonna temporanea per memorizzare i valori presenti nella colonna IDENTITY, rigenerare nuovi valori e aggiornare le tabelle collegate. È importante notare che non è possibile aggiornare direttamente, con un UPDATE, una colonna IDENTITY, anche impostando IDENTITY_INSERT a ON.
Da questo studio è nata la stored procedure sp_identity_rebirth che adotta una strategia multifase per rigenerare i valori di una colonna IDENTITY di una tabella, mantenendo l’integrità referenziale e minimizzando i rischi di perdita di dati.
La stored procedure include i seguenti passaggi:
- Controllo dei parametri di input
- Verifica che i nomi dello schema, della tabella e della colonna IDENTITY non siano vuoti
- Controlla che la colonna IDENTITY esista nella tabella specificata
- Verifica della chiave primaria
- Determina se la colonna IDENTITY è la chiave primaria della tabella, se non è la chiave primaria, la procedura si interrompe (in futuro potrà gestire anche questo caso)
- Preparazione alla rigenerazione dei valori IDENTITY
- Raccoglie i comandi T-SQL necessari in una tabella temporanea per eseguire le operazioni in ordine sequenziale
- Gestione delle chiavi esterne
- Identifica e rimuove le chiavi esterne che fanno riferimento alla colonna IDENTITY, PRIMARY KEY per evitare conflitti durante la rigenerazione
- Backup e manipolazione della tabella
- Aggiunge una colonna temporanea per memorizzare i valori IDENTITY correnti
- Crea una copia di backup della tabella originale
- Esegue il TRUNCATE della tabella originale per reimpostare i valori della colonna IDENTITY
- Reinserimento dei dati
- Reinserisce i dati dalla tabella di backup alla tabella originale, escludendo la colonna IDENTITY (che sarà rigenerata automaticamente)
- Aggiorna i riferimenti delle chiavi esterne per riflettere i nuovi valori della colonna IDENTITY
- Ripristino delle chiavi esterne
- Ricrea le chiavi esterne rimosse precedentemente
- Gestione della transazione e degli errori
- Apre una transazione esplicita se non ne esiste già una
- In caso di errore, esegue il rollback della transazione e solleva un errore
- Se tutti i comandi vengono eseguiti con successo, conferma la transazione
Questi i vantaggi nell’utilizzo della stored procedure sp_identity_rebirth:
- La procedura gestisce le chiavi esterne, assicurando che i riferimenti rimangano validi dopo la rigenerazione dei valori della colonna IDENTITY
- L’uso di una tabella temporanea per memorizzare i comandi SQL garantisce che le operazioni vengano eseguite nell’ordine corretto
- La transazione assicura che tutte le operazioni siano atomiche, riducendo il rischio di inconsistenze
La stored procedure sp_identity_rebirth è disponibile su questo repository GitHub: https://github.com/segovoni/sp_identity_rebirth
Non vi resta che provarla e… ogni contributo è ben accetto 🙂
