Warum SQL Server bei Identity und Sequence Nummern auslässt

Eine fehlende Identity-Nummer beweist nicht, dass jemand eine Zeile gelöscht hat. SQL Server kann eine Nummer für eine Einfügung vergeben, die später zurückgerollt wird. Die Nummernvergabe wird dabei nicht zurückgenommen. Wer Identity als lückenlosen Zähler interpretiert, erzeugt deshalb falsche Alarme und versucht möglicherweise gefährlicherweise, Kennungen wiederzuverwenden.

Eine technische Kennung beantwortet, welche Zeile gemeint ist. Eine fachliche Dokumentnummer kann dagegen eine Position im Ausstellungsprozess bezeichnen. Diese Anforderungen besitzen unterschiedliche Lebenszyklen. Getrennte Spalten verhindern, dass technische Speichereigenschaften unbemerkt die fachliche Nummerierung bestimmen.

Vergabe und Bestätigung unterscheiden

Das Beispiel verwendet eine temporäre Tabelle. Die erste bestätigte Zeile macht den Ablauf der Vergabe eindeutig sichtbar.

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;

Erhalten bleiben die Kennungen 1 und 3. Nummer 2 gehörte zur zurückgerollten Einfügung. Im endgültigen Datenbestand fehlt keine bestätigte Zeile: Der Rollback war korrekt, während die Vergabe weiterlief.

IDENTITY gehört zu einer Tabelle. Eine SEQUENCE ist dagegen ein eigenständiges Schemaobjekt, dessen Werte bereits vor einer Einfügung angefordert und von mehreren Tabellen verwendet werden können. Das hilft, wenn eine Kennung früh benötigt wird. Nicht verwendete Nummern sind dadurch aber ebenfalls normal. Auch Sequenzvergaben werden durch einen Transaktionsrollback nicht rückgängig gemacht.

Caching verbessert die Effizienz und kann weitere Lücken erzeugen, wenn ungenutzte zwischengespeicherte Werte bei einem unerwarteten Ausfall verloren gehen. Das Abschalten reduziert allenfalls diese Ursache. Es gibt weder zurückgerollte noch anderweitig ungenutzte Werte frei. NO CACHE bedeutet nicht lückenlos.

Eindeutigkeit muss unabhängig davon durch Primärschlüssel oder eindeutige Einschränkung abgesichert werden. Reseeding, explizite Identity-Werte oder zyklische Sequenzen können sonst Kollisionen verursachen. Solche Eingriffe gehören in kontrollierte Migrationen, nicht in eine regelmäßige Reparatur vermeintlicher Lücken.

Tatsächlich erzeugte Schlüssel zurückgeben

MAX(Id) plus eins ist keine sichere Vorhersage. Eine andere Sitzung kann zwischen Lesen und Schreiben einfügen. Auch die Differenz zwischen größter Kennung und Zeilenanzahl misst gelöschte Datensätze nicht zuverlässig.

SCOPE_IDENTITY liefert bei einer einzelnen Einfügung die zuletzt im aktuellen Gültigkeitsbereich erzeugte Identity. @@IDENTITY kann dagegen eine durch einen Trigger erzeugte Kennung liefern. Für mehrere Zeilen gibt OUTPUT inserted.Id die tatsächlich vergebenen Werte zurück. Deren Reihenfolge darf nicht als Reihenfolge der Eingabedaten angenommen werden; verwenden Sie einen ausdrücklichen Zuordnungswert.

OUTPUT-Werte beweisen nicht, dass die gesamte Transaktion später bestätigt wurde. Ein nachfolgender Fehler oder Rollback kann die Änderung noch verwerfen. Externe Benachrichtigungen benötigen bestätigten fachlichen Erfolg und ein Verfahren für unklare COMMIT-Ergebnisse.

Die Reihenfolge der Kennungen entspricht außerdem nicht zwingend der Bestätigungsreihenfolge. Eine Transaktion mit kleinerer Nummer kann später committen. Berichte brauchen eine passende Zeitangabe und einen definierten Sortiervertrag. Für verlustfreie Änderungskonsumenten ist ein geeigneter Änderungsmechanismus erforderlich.

Kapazität und Fachnummern getrennt planen

Diese Katalogabfrage zeigt Identity-Spalten und zuletzt vergebene Werte.

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;

Berechnen Sie den verbleibenden Bereich aus Datentyp, Startwert, Schrittweite und Vergaberate. Eine positive int-Identity hat eine endliche Obergrenze, und auch gescheiterte Einfügungen verbrauchen Werte. Das Löschen alter Zeilen vergrößert den Bereich nicht.

Eine Umstellung auf bigint kann Fremdschlüssel, zusätzliche Indizes, Parameter, Exporte und Anwendungstypen betreffen. Planen Sie sie rechtzeitig statt erst beim Erreichen der Grenze.

Benötigt das Geschäft eine kontrollierte Dokumentfolge, vergeben Sie die Fachnummer beim passenden Ausstellungsschritt und speichern Sie sie getrennt. Definieren Sie insbesondere die Darstellung stornierter Dokumente. Ein transaktionaler Zähler serialisiert möglicherweise die Vergabe und begrenzt damit den Durchsatz. Eine Aufteilung nach Mandant, Dokumentart oder Zeitraum ist nur zulässig, wenn die fachliche Regel dazu passt.

Prüfen Sie gleichzeitige Ausstellung, Stornierung, Rollback und Wiederholung um COMMIT herum. Entscheidend ist eine erklärte und durchgesetzte Nummerierungsregel, nicht bloß das optische Fehlen von Lücken.

Technische Referenzen: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.