Welche ist die beste Option, um den Identitätswert zu erhalten, den ich gerade über eine Einfügung erzeugt habe? Welche Auswirkungen haben diese Anweisungen in Bezug auf die Leistung?
MAX()
TOP 1
IdentityColumn FROM TabellenName ORDER BY IdentityColumn DESC
Verwenden Sie SCOPE_IDENTITY()
, wenn Sie eine einzelne Zeile einfügen und die erzeugte ID abrufen wollen.
CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));
INSERT #a(x) VALUES('a');
SELECT SCOPE_IDENTITY();
Ergebnis:
----
1
Verwenden Sie die OUTPUT
-Klausel, wenn Sie mehrere Zeilen einfügen und den Satz der erzeugten IDs abrufen müssen.
INSERT #a(x)
OUTPUT inserted.identity_column
VALUES('b'),('c');
Ergebnis:
----
2
3
und warum ist dies die beste und schnellste Option?
Abgesehen von der Leistung sind dies die einzigen Optionen, die in der Standardisolationsebene und/oder bei mehreren Benutzern garantiert korrekt sind. Selbst wenn Sie den Aspekt der Korrektheit ignorieren, hält SQL Server den eingefügten Wert in SCOPE_IDENTITY()
im Speicher, so dass dies natürlich schneller ist, als wenn Sie Ihre eigene isolierte Abfrage gegen die Tabelle oder gegen Systemtabellen ausführen.
Den Aspekt der Korrektheit zu ignorieren ist so, als würde man dem Postboten sagen, dass er heute gute Arbeit geleistet hat - er hat seine Route 10 Minuten schneller als seine Durchschnittszeit beendet, das Problem ist nur, dass keine der Post an das richtige Haus geliefert wurde.
Verwenden Sie keinen der folgenden Begriffe:
@@IDENTITY
- da dies nicht in allen Szenarien verwendet werden kann, z.B. wenn eine Tabelle mit einer Identitätsspalte einen Trigger hat, der auch in eine andere Tabelle mit ihrer eigenen Identitätsspalte einfügt - Sie werden den falschen Wert zurückbekommen.MAX()
oder TOP 1
- Sie müssten die beiden Anweisungen mit serialisierbarer Isolation schützen, um sicherzustellen, dass die MAX()
, die Sie erhalten, nicht die eines anderen ist. Dies ist viel teurer als die Verwendung von SCOPE_IDENTITY()
.Diese Funktionen versagen auch immer dann, wenn Sie zwei oder mehr Zeilen einfügen und alle Identitätswerte generiert haben müssen - Ihre einzige Option ist hier die OUTPUT
-Klausel.
Abgesehen von der Leistung haben sie alle recht unterschiedliche Bedeutungen.
Mit SCOPE_IDENTITY()
erhalten Sie den letzten Identitätswert, der in jede Tabelle direkt innerhalb des aktuellen Bereichs eingefügt wurde (Bereich = Batch, gespeicherte Prozedur usw., aber nicht z.B. innerhalb eines Triggers, der durch den aktuellen Bereich ausgelöst wurde).
IDENT_CURRENT() gibt Ihnen den letzten Identitätswert, der in eine bestimmte Tabelle aus einem beliebigen Bereich von einem beliebigen Benutzer eingefügt wurde.
Mit @@IDENTITY
erhalten Sie den letzten Identitätswert, der durch die letzte INSERT-Anweisung für die aktuelle Verbindung erzeugt wurde, unabhängig von Tabelle oder Bereich. (Nebenbei bemerkt: Access verwendet diese Funktion und hat daher einige Probleme mit Triggern, die Werte in Tabellen mit Identitätsspalten einfügen).
Die Verwendung von MAX()
oder TOP 1
kann völlig falsche Ergebnisse liefern, wenn die Tabelle einen negativen Identitätsschritt hat oder Zeilen mit SET IDENTITY_INSERT
eingefügt wurden. Hier ist ein Skript, das alle diese Punkte demonstriert:
CREATE TABLE ReverseIdent (
id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
data char(4)
)
INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')
SELECT * FROM ReverseIdent
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000
SET IDENTITY_INSERT ReverseIdent ON
INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')
SET IDENTITY_INSERT ReverseIdent OFF
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005
Zusammenfassung: Bleiben Sie bei SCOPE_IDENTITY()
, IDENT_CURRENT()
, oder @@IDENTITY
, und stellen Sie sicher, dass Sie diejenige verwenden, die das zurückgibt, was Sie tatsächlich brauchen.