Pourquoi Identity et Sequence laissent des trous dans SQL Server

Une valeur identity manquante ne prouve pas qu'une ligne a été supprimée. SQL Server peut attribuer un numéro à une insertion ensuite annulée, sans annuler cette attribution. Considérer une colonne identity comme un compteur sans trous produit donc de fausses alertes et pousse parfois à réutiliser dangereusement des identifiants.

Un identifiant technique répond à la question "quelle ligne?". Un numéro métier peut représenter la position d'un document dans un processus d'émission. Ces besoins ont des cycles de vie différents. Les séparer empêche le fonctionnement du stockage de définir implicitement la politique métier.

Observer l'attribution avant la validation

Cet exemple utilise une table temporaire et conserve une première ligne validée pour rendre la séquence claire.

CREATE TABLE #Tickets (
    TicketId int IDENTITY(1,1) PRIMARY KEY,
    Note nvarchar(80) NOT NULL
);
INSERT #Tickets(Note) VALUES (N'First committed row');
BEGIN TRANSACTION;
INSERT #Tickets(Note) VALUES (N'This row is rolled back');
ROLLBACK TRANSACTION;
INSERT #Tickets(Note) VALUES (N'Next committed row');
SELECT TicketId, Note FROM #Tickets ORDER BY TicketId;
DROP TABLE #Tickets;

Les identifiants restants sont 1 et 3. Le numéro 2 avait été attribué à l'insertion annulée. Aucune ligne validée ne manque: la transaction a bien supprimé son écriture, tandis que l'allocateur a continué.

IDENTITY appartient à une table. SEQUENCE est un objet de schéma indépendant dont les valeurs peuvent être demandées avant l'insertion et partagées entre tables. C'est utile lorsqu'une clé doit être connue à l'avance, mais les attributions inutilisées deviennent normales. Les valeurs de séquence ne sont pas récupérées par un rollback.

Le cache améliore l'efficacité et peut créer d'autres trous si des valeurs réservées mais inutilisées sont perdues lors d'un arrêt inattendu. Désactiver le cache peut réduire cette cause, pas récupérer les valeurs annulées ou abandonnées. NO CACHE ne promet donc pas une suite continue.

Distinguez également génération et unicité. Une clé primaire ou une contrainte unique doit protéger l'identifiant stocké. Un réamorçage, des insertions identity explicites ou une séquence cyclique peuvent sinon créer des collisions. Ce sont des opérations de migration contrôlées, pas des réparations ordinaires de trous.

Récupérer les valeurs réellement produites

Lire MAX(Id) puis ajouter un n'est pas une prévision sûre. Une autre session peut insérer entre les deux étapes. La différence entre le maximum et le nombre de lignes n'est pas non plus un décompte fiable des suppressions.

Pour une insertion unique, SCOPE_IDENTITY retourne la dernière identity du même périmètre. @@IDENTITY peut refléter celle créée par un déclencheur dans un autre périmètre. Pour plusieurs lignes, OUTPUT inserted.Id fournit les clés réelles, mais son ordre ne doit pas être supposé identique à celui de l'entrée. Conservez une valeur explicite de corrélation.

Les valeurs retournées par OUTPUT ne prouvent pas la validation finale. Une erreur ultérieure ou un rollback peut encore annuler l'écriture. Les notifications externes doivent suivre un achèvement métier confirmé et prévoir le cas d'une validation incertaine.

L'ordre des identifiants n'est pas nécessairement celui des commits. Une transaction peut obtenir une petite valeur puis se terminer après une autre. Utilisez une date explicite et un contrat d'ordre pour les rapports, et un mécanisme de capture adapté lorsqu'un consommateur ne doit manquer aucun changement.

Anticiper capacité et numérotation métier

Cette requête recense les colonnes identity et leurs dernières valeurs attribuées.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS ColumnName,
       TYPE_NAME(user_type_id) AS DataType,
       seed_value, increment_value, last_value
FROM sys.identity_columns
ORDER BY SchemaName, TableName;

Surveillez la capacité restante selon le type, la valeur initiale, le sens de l'incrément et la vitesse d'attribution. Une identity int positive possède une limite finie; les insertions échouées consomment aussi des numéros. Supprimer les anciennes lignes ne repousse pas cette limite.

Passer à bigint peut toucher les clés étrangères, index secondaires, paramètres, exports et types de l'application. Préparez la migration avant l'épuisement plutôt que comme une modification urgente de colonne.

Si le métier exige une séquence documentaire contrôlée, attribuez le numéro au bon moment de l'émission, stockez-le séparément et définissez les annulations. Un compteur transactionnel peut sérialiser l'attribution et limiter le débit. Le répartir par locataire, catégorie ou période n'est valable que si la règle métier le permet.

Testez émission concurrente, annulation, rollback et reprise autour du commit. La garantie utile est une politique explicite et appliquée, pas l'apparence de nombres consécutifs dans une colonne technique.

Références techniques: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.