SCOPE_IDENTITY (Transact-SQL)
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance
Renvoie la dernière valeur d'identité insérée dans une colonne d'identité dans la même étendue. Une étendue est un module : procédure stockée, déclencheur, fonction ou lot. Par conséquent, deux instructions sont dans la même étendue si elles se trouvent dans la même procédure stockée ou fonction, ou dans le même lot.
Conventions de la syntaxe Transact-SQL
Syntaxe
SCOPE_IDENTITY()
Types de retour
numeric(38,0)
Remarques
SCOPE_IDENTITY, IDENT_CURRENT et @@IDENTITY sont des fonctions similaires car elles renvoient des valeurs insérées dans des colonnes d'identité.
IDENT_CURRENT n'est pas limitée par l'étendue et par la session ; elle est limitée à une table spécifiée. IDENT_CURRENT renvoie la valeur générée pour une table spécifique dans n'importe quelle session et n'importe quelle étendue. Pour plus d’informations, consultez IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY et @@IDENTITY renvoient les dernières valeurs d'identité générées dans une table de la session active. Toutefois, SCOPE_IDENTITY renvoie les valeurs insérées uniquement dans l'étendue actuelle. @@IDENTITY n'est pas limitée à une étendue spécifique.
Par exemple, deux tables T1 et T2 et un déclencheur INSERT sont définis sur T1. Lorsqu'une ligne est insérée dans T1, le déclencheur est activé et insère une ligne dans T2. Ce scénario met en œuvre deux étendues : l'insertion dans T1 et l'insertion dans T2 par le déclencheur.
Supposons que T1 et T2 comportent les colonnes d'identité, @@IDENTITY et SCOPE_IDENTITY renvoient des valeurs différentes à la fin d'une instruction INSERT sur T1. @@IDENTITY renvoie la dernière valeur de la colonne d’identité insérée dans toutes les étendues au cours de la session en cours. Il s'agit de la valeur insérée dans T2. SCOPE_IDENTITY() renvoie la valeur IDENTITY insérée dans T1. Il s'agit de la dernière insertion qui s'est produite dans la même étendue. La fonction SCOPE_IDENTITY() renvoie la valeur NULL si la fonction est appelée avant qu’une instruction INSERT dans une colonne d’identité soit exécutée dans l’étendue.
Les instructions et les transactions en échec peuvent modifier l'identité actuelle d'une table et créer des trous dans les valeurs des colonnes d'identité. La valeur d'identité n'est jamais annulée, même si la transaction qui a essayé d'insérer la valeur dans la table n'est pas validée. Par exemple, si une instruction INSERT échoue à cause d'une violation d'identité IGNORE_DUP_KEY, la valeur d'identité actuelle de la table augmente quand même d'une unité.
Exemples
R. Utilisation des fonctions @@IDENTITY et SCOPE_IDENTITY avec des déclencheurs
L'exemple suivant crée deux tables, TZ et TY, ainsi qu'un déclencheur INSERT sur TZ. Lorsqu'une ligne est insérée dans la table TZ, le déclencheur (Ztrig) est activé et insère une ligne dans TY.
USE tempdb;
GO
CREATE TABLE TZ (
Z_id INT IDENTITY(1,1)PRIMARY KEY,
Z_name VARCHAR(20) NOT NULL);
INSERT TZ
VALUES ('Lisa'),('Mike'),('Carla');
SELECT * FROM TZ;
Jeu de résultats : Voici à quoi ressemble la table TZ :
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'), ('rocks'), ('elevator');
SELECT * FROM TY;
Jeu de résultats : Voici à quoi ressemble TY :
Y_id Y_name
---------------
100 boathouse
105 rocks
110 elevator
Créez le déclencheur qui insère une ligne dans la table TY lorsqu’une ligne est insérée dans la table TZ.
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END;
FIRE le déclencheur et déterminez quelles valeurs d’identité vous obtenez avec les fonctions @@IDENTITY et SCOPE_IDENTITY.
INSERT TZ VALUES ('Rosalie');
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO
Voici le jeu de résultats.
/*SCOPE_IDENTITY returns the last identity value in the same scope. This was the insert on table TZ.*/`
SCOPE_IDENTITY
4
/*@@IDENTITY returns the last identity value inserted to TY by the trigger.
This fired because of an earlier insert on TZ.*/
@@IDENTITY
115
B. Utilisation des fonctions @@IDENTITY et SCOPE_IDENTITY() avec la réplication
Les exemples suivants indiquent comment utiliser @@IDENTITY et SCOPE_IDENTITY() pour des insertions dans une base de données qui est publiée pour la réplication de fusion. Les deux tables mentionnées à titre d'exemple appartiennent à la base de données exemple AdventureWorks2022 : Person.ContactType n'est pas publiée et Sales.Customer est publiée. La réplication de fusion ajoute des déclencheurs aux tables qui sont publiées. Par conséquent, @@IDENTITY peut retourner la valeur de l'insertion dans une table système de réplication au lieu de l'insertion dans une table utilisateur.
La table Person.ContactType a une valeur d’identité maximale de 20. Si vous insérez une ligne dans la table, @@IDENTITY et SCOPE_IDENTITY() retournent la même valeur.
USE AdventureWorks2022;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO
Voici le jeu de résultats.
SCOPE_IDENTITY
21
@@IDENTITY
21
La table Sales.Customer possède une valeur d'identité maximale de 29483. Si vous insérez une ligne dans la table, @@IDENTITY et SCOPE_IDENTITY() retournent des valeurs différentes. SCOPE_IDENTITY() retourne la valeur de l'insertion dans la table utilisateur, alors que @@IDENTITY retourne la valeur de l'insertion dans la table système de réplication. Utilisez SCOPE_IDENTITY() pour les applications qui doivent accéder à la valeur d'identité insérée.
INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO
Voici le jeu de résultats.
SCOPE_IDENTITY
29484
@@IDENTITY
89