SCOPE_IDENTITY (Transact-SQL)
Gibt den letzten Identitätswert zurück, der in eine Identitätsspalte im selben Gültigkeitsbereich eingefügt wurde. Ein Gültigkeitsbereich ist ein Modul: eine gespeicherte Prozedur, ein Trigger, eine Funktion oder ein Batch. Daher befinden sich zwei Anweisungen im selben Gültigkeitsbereich, wenn sie sich in derselben gespeicherten Prozedur, in derselben Funktion oder im selben Batch befinden.
.gif "Themenlink (Symbol)")
Transact-SQL-Syntaxkonventionen
Syntax
SCOPE_IDENTITY()
Rückgabetypen
numeric(38,0)
Hinweise
Bei SCOPE_IDENTITY, IDENT_CURRENT und @@IDENTITY handelt es sich um ähnliche Funktionen, denn sie geben Werte zurück, die in Identitätsspalten eingefügt wurden.
IDENT_CURRENT ist nicht durch einen Gültigkeitsbereich oder eine Sitzung begrenzt, sondern auf eine angegebene Tabelle. IDENT_CURRENT gibt den für eine bestimmte Tabelle in einer beliebigen Sitzung und einem beliebigen Gültigkeitsbereich generierten Wert zurück. Weitere Informationen finden Sie unter IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY und @@IDENTITY geben die letzten Identitätswerte zurück, die in einer beliebigen Tabelle in der aktuellen Sitzung generiert wurden. SCOPE_IDENTITY gibt jedoch nur im aktuellen Gültigkeitsbereich eingefügte Werte zurück, @@IDENTITY ist nicht auf einen bestimmten Gültigkeitsbereich begrenzt.
Beispiel: Es gibt zwei Tabellen, T1 und T22, und für T1 wurde ein INSERT-Trigger definiert. Wenn eine Zeile in T1 eingefügt wird, wird der Trigger ausgelöst und fügt eine Zeile in T2 ein. Dieses Szenario veranschaulicht zwei Gültigkeitsbereiche: die Einfügung für T1 und die Einfügung für T2 durch den Trigger.
Wenn T1 und T2 Identitätsspalten aufweisen, geben @@IDENTITY und SCOPE_IDENTITY nach einer INSERT-Anweisung für T1 verschiedene Werte zurück. @@IDENTITY gibt den letzten Identitätsspaltenwert zurück, der in einem beliebigen Gültigkeitsbereich in der aktuellen Sitzung eingefügt wurde. Das ist der Wert, der in T2 eingefügt wurde. SCOPE_IDENTITY() gibt den IDENTITY-Wert zurück, der in T1 eingefügt wurde. Dies war die letzte Einfügung, die im selben Gültigkeitsbereich durchgeführt wurde. Die SCOPE_IDENTITY()-Funktion gibt den NULL-Wert zurück, wenn sie aufgerufen wird, bevor INSERT-Anweisungen für eine Identitätsspalte im Gültigkeitsbereich auftreten.
Anweisungen und Transaktionen mit Fehlern können die aktuelle Identität einer Tabelle verändern und Lücken in den Identitätsspaltenwerten entstehen lassen. Für den Identitätswert erfolgt kein Rollback, auch wenn für die Transaktion, die versuchte, den Wert in die Tabelle einzufügen, kein Commit ausgeführt wird. Erzeugt eine INSERT-Anweisung beispielsweise aufgrund einer Verletzung von IGNORE_DUP_KEY einen Fehler, wird der aktuelle Identitätswert für die Tabelle dennoch erhöht.
Beispiele
A.Verwenden von @@IDENTITY und SCOPE_IDENTITY mit Triggern
Im folgenden Beispiel werden zwei Tabellen, TZ und TY, sowie ein INSERT-Trigger für TZ erstellt. Wenn eine Zeile in die Tabelle TZ eingefügt wird, wird der Trigger (Ztrig) ausgelöst und fügt eine Zeile in TY ein.
USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)
INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks.
Z_id Z_name
-------------
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)
INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
---------------
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ.*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END
/*FIRE the trigger and determine what identity values you obtain
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO
Dies ist das Resultset.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope. This was the insert on table TZ.*/
@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger. This fired because of an earlier insert on TZ.*/
B.Verwenden von @@IDENTITY und SCOPE_IDENTITY() mit Replikation
In den folgenden Beispielen wird veranschaulicht, wie @@IDENTITY und SCOPE_IDENTITY() für Einfügungen in einer Datenbank verwendet werden, die für die Mergereplikation veröffentlicht wird. Beide Tabellen in den Beispielen befinden sich in der AdventureWorks2012 -Beispieldatenbank: Person.ContactType wird nicht veröffentlicht, und Sales.Customer wird veröffentlicht. Mit der Mergereplikation werden Tabellen, die veröffentlicht werden, Trigger hinzugefügt. Aus diesem Grund kann @@IDENTITY den Wert aus dem Einfügevorgang in eine Replikationssystemtabelle zurückgeben statt aus dem Einfügevorgang in eine Benutzertabelle.
Die Person.ContactType-Tabelle besitzt den maximalen Identitätswert 20. Wenn Sie eine Zeile in die Tabelle einfügen, wird von @@IDENTITY und SCOPE_IDENTITY() derselbe Wert zurückgegeben.
USE AdventureWorks2012;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO
Dies ist das Resultset.
SCOPE_IDENTITY
21
@@IDENTITY
21
Die Sales.Customer-Tabelle besitzt den maximalen Identitätswert 29483. Wenn Sie eine Zeile in die Tabelle einfügen, werden von @@IDENTITY und SCOPE_IDENTITY() jeweils unterschiedliche Werte zurückgegeben. SCOPE_IDENTITY() gibt den Wert aus dem Einfügevorgang in die Benutzertabelle zurück, @@IDENTITY hingegen gibt den Wert aus dem Einfügevorgang in die Replikationssystemtabelle zurück. Verwenden Sie SCOPE_IDENTITY() für Anwendungen, für die der Zugriff auf den eingefügten Identitätswert erforderlich ist.
INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO
Dies ist das Resultset.
SCOPE_IDENTITY
29484
@@IDENTITY
89