Dépassement arithmétique lors de la conversion IDENTITY vers INT

Comment contourner l’erreur de dépassement arithmétique lors de la conversion IDENTITY en type de données INT avec SQL Server ?

En T-SQL, l’erreur de dépassement arithmétique lors de la conversion IDENTITY en type de données int, est un message d’erreur qui s’affiche si on tente d’insérèrer une nouvelle ligne dans une table avec une colonne IDENTITY. Et que cette valeur attribuée à la colonne IDENTITY dépasse la valeur maximale pouvant être stockée dans un type de données SQL Server INT, c’est à dire 2147483647.

1. Dépassement arithmétique lors d’une insertion standard

Créer tout d’abord la première table de test et y insérer 3 lignes différentes.

DROP table TestTable;

CREATE TABLE TestTable (
    ID   INT IDENTITY(2147483647,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable OFF;

-- La première insertion fonctionne
INSERT INTO TestTable (Col1)
VALUES (1);

-- La deuxième génère une erreur
INSERT INTO TestTable (Col1)
VALUES (2);

La dernière insertion échouera donc avec le message suivant :

• En Francais : Erreur de dépassement arithmétique lors de la conversion IDENTITY en type de données int.
• Ou en Anglais : Arithmetic overflow error converting IDENTITY to data type int.

Pour contourner cette erreur, changer le type de données de la colonne IDENTITY pour un type de données plus grand, comme le type BIGINT par exemple.

CREATE TABLE TestTable (
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

-- L'insertion fonctionne maintenant
INSERT INTO TestTable (Col1)
VALUES (2147483648);

2. Dépassement arithmétique avec l’option IDENTITY

Insérer une ligne dans une table avec une colonne IDENTITY après avoir utiliser l’instruction SET IDENTITY_INSERT. Le but est de spécifier que la colonne marquée avec l’option IDENTITY doit être incluse dans l’insertion.

Ce deuxième cas de figure est similaire mais l’option d’insertion explicite de la colonne de type identité est activée. Il est donc nécessaire de

CREATE TABLE TestTable2 (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable2 ON;

-- Ce code échoue avec le message d'eerur de dépassement arithmétique
INSERT INTO TestTable2 (ID, Col1)
VALUES (2147483648, 1);

Une solution de contournement possible pour cette erreur de conversion courante consiste à modifier le type de données de la colonne IDENTITY en un type de données plus grand, comme BIGINT.

DROP TABLE IF EXISTS TestTable2;

CREATE TABLE TestTable2 (
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable2 ON;

-- L'insertion de données fonctionne maintenant
INSERT INTO TestTable2 (ID, Col1)
VALUES (363372036854775807, 1);

3. Dépassement arithmétique à cause de la première valeur

Voici un troisième exemple de code T-SQL qui pourrait générer l’erreur de dépassement arithmétique.

CREATE TABLE test (
    ID INT IDENTITY(2147483647, 1), 
    Valeur VARCHAR(50)
);

-- Instruction d'insertion en erreur
INSERT INTO test (Valeur)
VALUES ('test') ;

La propriété IDENTITY est utilisée pour indiquer que la colonne « id » est une colonne d’identité, ce qui signifie qu’une valeur unique lui sera automatiquement attribuée pour chaque nouvelle ligne insérée dans la table. Dans cet exemple, la propriété IDENTITY est définie pour commencer à la valeur maximale pour un type de données INT (2147483647) et pour s’incrémenter de 1 pour chaque nouvelle ligne.

Cela provoquera une erreur de dépassement arithmétique lorsque la prochaine valeur à attribuer à la colonne « id » dépassera la valeur maximale pouvant être stockée dans un type de données INT (2147483647).

Pour corriger cette erreur, vous pouvez soit changer le type de données de la colonne « id » pour un type de données plus grand qui peut contenir des valeurs plus importantes (comme BIGINT), soit spécifier une valeur de départ et/ou un incrément plus petit pour la propriété IDENTITY.

Cette fois, changer le nombre de départ de l’incrémentation. Cela créera la table « test » avec une colonne « id » qui commence à 1 et s’incrémente de 1 pour chaque nouvelle ligne, ce qui ne devrait pas provoquer une « erreur de dépassement arithmétique ». Par exemple :

CREATE TABLE test (
    ID INT IDENTITY(1, 1), 
    Valeur VARCHAR(50)
);

INSERT INTO test (Valeur)
VALUES ('test');

D’autre part, une requête SQL Server de sélection ou d’insertion, copie les type de données des colonnes depuis la table source vers la table créée et les valeurs. Or certaines valeurs utilisées comme ID dans certaines tables non triées peuvent atteindre rapidement le plus grand entier permis par le type de données INT (entier).

4. Conclusion sur le dépassement arithmétique d’IDENTITY

Pour éviter l’erreur de dépassement arithmétique lors de la conversion IDENTITY en type de données INT avec SQL Server, voici 4 bonnes pratiques à suivre.

1. Utilisez un type de données plus grand pour la colonne IDENTITY, comme bigint, pour permettre une plage de valeurs plus étendue.
2. Lors de l’utilisation de l’instruction SET IDENTITY_INSERT table_name ON pour insérer une valeur dans la colonne IDENTITY, assurez-vous que la valeur est dans la plage du type de données de la colonne IDENTITY.
3. Lors de l’utilisation de l’instruction SELECT INTO pour créer une nouvelle table avec une colonne IDENTITY, spécifiez un type de données plus grand pour la colonne IDENTITY ou désactivez la propriété IDENTITY et réactivez-la après avoir inséré les données.
4. Vérifiez régulièrement les valeurs de la colonne IDENTITY pour vous assurer qu’elles ne s’approchent pas de la valeur maximale pour le type de données. Si nécessaire, réinitialisez la valeur IDENTITY à une valeur inférieure pour éviter de dépasser la valeur maximale.

Nom d’objet non valide avec un CTE SQL Server