Was ist IDENTITY?
IDENTITY ist eine Eigenschaft für Spalten einer Tabelle. Sie kann verwendet werden, um Schlüssel zu erzeugen, also eindeutige, fortlaufende Werte in einer Spalte. Dabei garantiert der SQL Server, dass auch mehrere parallel laufende Transaktionen verschiedene Werte für die Spalte erhalten.
Wie könnt ihr mit IDENTITY arbeiten?
Die IDENTITY-Eigenschaft bekommt zwei Werte übergeben, den SEED, also den Startwert, den die erste Zeile in der Tabelle enthält und den INCREMENT, also den Wert, um wieviel der aktuelle Wert nach jeder Zeile erhöht werden soll. Üblich ist es, beide Werte auf eins zu setzen. Legen wir uns also einmal eine Tabelle mit IDENTITY-Spalte an:
DROP TABLE IF EXISTS #IdTest
CREATE TABLE #IdTest
(
Id int IDENTITY(1,1)
,Txt varchar(250)
)
Nun fügen wir eine Zeile in diese Tabelle ein und fragen sie ab:
INSERT INTO #IdTest (Txt)
VALUES ('Test')
SELECT
Id
,Txt
FROM #IdTest
Ihr seht eine Zeile mit dem Text “Test” und dem ID-Wert von eins. Fügt ihr nun zwei weitere Spalten hinzu, dann erhalten diese die Id-Werte zwei und drei:
INSERT INTO #IdTest (Txt)
VALUES ('Test2'), ('Test3')
SELECT
Id
,Txt
FROM #IdTest
Euch ist als erprobte Ninjas sicher aufgefallen, dass wir die Id-Spalte bei den INSERT-Statements ausgelassen hatten, da diese als IDENTITY definiert ist und daher vom SQL Server selber gefüllt wird. Versuchen wir nun also, diese Spalte mit anzugeben:
INSERT INTO #IdTest (Id, Txt) VALUES (4, 'Test4')
Dann erhalten wir eine Fehlermeldung, die besagt, dass es nicht möglich ist, in eine IDENTITY-Spalte einzufügen, wenn IDENTITY_INSERT ausgeschaltet ist. Ändern wir das also:
SET IDENTITY_INSERT #IdTest ON
INSERT INTO #IdTest (Id, Txt)
VALUES (4, 'Test4')
SET IDENTITY_INSERT #IdTest OFF
SELECT
Id
,Txt
FROM #IdTest
Nun enthält unsere Tabelle eine weitere Zeile und wenn wir noch eine Zeile einfügen, dann erhält diese sogar korrekterweise die Id 5:
INSERT INTO #IdTest (Txt)
VALUES ('Test2'), ('Test3')
SELECT
Id
,Txt
FROM #IdTest
Fügen wir nun also eine Id ein, die nicht zur aktuellen Reihe passt und hinterher bei eingeschalteter IDENTITY-Funktion eine weitere Zeile:
SET IDENTITY_INSERT #IdTest ON
INSERT INTO #IdTest (Id, Txt)
VALUES (60, 'Test6')
SET IDENTITY_INSERT #IdTest OFF
INSERT INTO #IdTest (Txt)
VALUES ('Test5')
SELECT
Id
,Txt
FROM #IdTest
Nun haben die beiden eingefügten Zeilen die Ids 60 (wo wir sie explizit angegeben hatten) bzw. 61 erhalten, wo wir sie automatisch vergeben hatten. Die IDENTITY-Spalte hat also “gemerkt”, dass ihr aktueller Maximal-Wert momentan die 60 ist und hat automatisch die 61 als nächsten Wert vergeben.
Was der nächste Wert der Identity-Spalte wäre, können wir über ein DBCC-Kommando prüfen:
DBCC CHECKIDENT ('#IdTest')
Das DBCC-Kommando kann auch verwendet werden, um den aktuellen SEED der IDENTITY zurückzusetzen:
DBCC CHECKIDENT ('#IdTest', RESEED, 1)
Hiernach gibt die erste CHECKIDENT-Abfrage die Information zurück, dass der aktuelle IDENTITY-Wert eins ist, während der aktuelle Spaltenwert bei 100 liegt. Dennoch wird beim nächsten INSERT wieder automatisch der Maximalwert der Spalte geprüft und die nächste Zeile erhält die ID 101:
INSERT INTO #IdTest (Txt)
VALUES ('Test8')
SELECT
Id
,Txt
FROM #IdTest
Nun möchten wir etwas über die Transaktionssicherheit von IDENTITY-Spalten lernen. Dafür öffnen wir eine Transaktion und fügen eine Zeile in die Tabelle ein, die wir direkt abfragen:
BEGIN TRANSACTION
INSERT INTO #IdTest (Txt)
VALUES ('Transaction 1')
SELECT
Id
,Txt
FROM #IdTest
Wir sehen, dass unsere neue Zeile erwartungsgemäß die nächste Id (101) bekommen hat.
Rollen wir nun die Transaktion zurück, so ist natürlich die Zeile nicht mehr in der Tabelle enthalten:
ROLLBACK TRANSACTION
SELECT
Id
,Txt
FROM #IdTest
Prüfen wir nun den Wert der Identity-Spalte, dann sehen wir, dass der IDENTITY-Wert nun auf 101 stehengeblieben ist:
DBCC CHECKIDENT ('#IdTest')
Die nächste eingefügte Zeile erhält also den Id-Wert 102, die 101 wurde durch das Zurückrollen der Transaktion übersprungen:
INSERT INTO #IdTest (Txt)
VALUES ('Transaction 1')
SELECT
Id
,Txt
FROM #IdTest
Ihr habt gesehen, dass Spalten mit dem IDENTITY-Attribut automatisch den kleinstmöglichen nächsten freien Wert in der Spalte übernehmen. Dabei kann es zu Lücken kommen. Und seid gewarnt: es kann trotz Identity-Spalte durch IDENTITY_INSERTs dazu kommen, dass Duplikate in der Spalte enthalten sind. Wenn ihr das vermeiden möchtet, müsst ihr die Spalte zusätzlich mit dem UNIQUE-Attribut versehen.
