SCOPE_IDENTITY (Transact-SQL)

Restituisce l'ultimo valore Identity inserito in una colonna Identity nello stesso ambito. Un ambito è un modulo, ovvero una stored procedure, un trigger, una funzione o un batch. Due istruzioni appartengono pertanto allo stesso ambito se sono incluse nella stessa stored procedure, nella stessa funzione o nello stesso batch.

Convenzioni della sintassi Transact-SQL

Sintassi

SCOPE_IDENTITY()

Tipi restituiti

numeric(38,0)

Osservazioni

Le funzioni SCOPE_IDENTITY, IDENT_CURRENT e @@IDENTITY sono simili in quanto restituiscono valori inseriti in colonne Identity.

Per la funzione IDENT_CURRENT non esiste alcuna restrizione di ambito o di sessione. La funzione è limitata tuttavia a una tabella specifica. La funzione IDENT_CURRENT restituisce il valore generato per una tabella specifica in qualsiasi sessione e in qualsiasi ambito. Per ulteriori informazioni, vedere IDENT_CURRENT (Transact-SQL).

Le funzioni SCOPE_IDENTITY e @@IDENTITY restituiscono gli ultimi valori Identity generati in qualsiasi tabella durante la sessione corrente. SCOPE_IDENTITY, tuttavia, restituisce i valori inseriti solo nell'ambito corrente, mentre @@IDENTITY non è limitata a un ambito specifico.

Si supponga, ad esempio, che esistano due tabelle, T1 e T2, e che venga definito un trigger INSERT per T1. Quando viene inserita una riga in T1, viene attivato il trigger che inserisce una riga in T2. Questo scenario illustra due ambiti, ovvero l'inserimento in T1 e l'inserimento in T2 da parte del trigger.

Se entrambe le tabelle T1 e T2 contengono colonne Identity, @@IDENTITY e SCOPE_IDENTITY restituiranno valori diversi al termine dell'esecuzione di un'istruzione INSERT su T1. @@IDENTITY restituirà l'ultimo valore inserito in una colonna Identity in qualsiasi ambito durante la sessione corrente, ovvero il valore inserito in T2. SCOPE_IDENTITY() restituirà il valore IDENTITY inserito in T1, che rappresenta l'ultimo inserimento eseguito nello stesso ambito. Se viene richiamata prima che nell'ambito specifico venga eseguita un'istruzione INSERT in una colonna Identity, la funzione SCOPE_IDENTITY() restituirà il valore Null.

Le istruzioni e le transazioni con esito negativo sono in grado di modificare i dati Identity correnti di una tabella e creare gap nei valori della colonna Identity. Non viene mai eseguito il rollback del valore Identity, anche se non si esegue il commit della transazione che ha tentato l'inserimento del valore nella tabella. Se, ad esempio, un'istruzione INSERT ha esito negativo a causa di una violazione di IGNORE_DUP_KEY, il valore Identity corrente per la tabella viene comunque incrementato.

Esempi

A. Utilizzo di @@IDENTITY e SCOPE_IDENTITY con trigger

Nell'esempio seguente vengono create due tabelle, TZ e TY, e viene definito un trigger INSERT per TZ. Quando viene inserita una riga nella tabella TZ, viene attivato il trigger (Ztrig) che inserisce una riga in 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')
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

Set di risultati:

SCOPE_IDENTITY

4

/*SCOPE_IDENTITY ha restituito l'ultimo valore Identity nello stesso ambito. Il valore è stato inserito nella tabella TZ.*/

@@IDENTITY

115

/*@@ IDENTITY ha restituito l'ultimo valore Identity inserito in TY dal trigger. Questo evento è stato generato a causa di un precedente inserimento in TZ.*/

B. Utilizzo di @@IDENTITY e SCOPE_IDENTITY() con la replica

Negli esempi seguenti viene illustrato come utilizzare@@IDENTITY e SCOPE_IDENTITY() per inserimenti in un database pubblicato per la replica di tipo merge. Entrambe le tabelle degli esempi sono incluse nel database di esempio AdventureWorks2008R2. La tabella Person.ContactType non viene pubblicata, mentre la tabella Sales.Customer viene pubblicata. Con la replica di tipo merge vengono aggiunti trigger alle tabelle pubblicate. @@IDENTITY può pertanto restituire il valore dell'inserimento in una tabella del sistema di replica anziché dell'inserimento in una tabella utente.

Il valore Identity massimo consentito per la tabella Person.ContactType è 20. Se si inserisce una riga nella tabella, @@IDENTITY e SCOPE_IDENTITY() restituiscono lo stesso valore.

USE AdventureWorks2008R2;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

Set di risultati:

SCOPE_IDENTITY

21

@@IDENTITY

21

Il valore Identity massimo consentito per la tabella Sales.Customer è 29483. Se si inserisce una riga nella tabella, @@IDENTITY e SCOPE_IDENTITY() restituiscono valori diversi. SCOPE_IDENTITY() restituisce il valore dell'inserimento nella tabella utente, mentre @@IDENTITY restituisce il valore dell'inserimento nella tabella del sistema di replica. Utilizzare SCOPE_IDENTITY() per le applicazioni che necessitano dell'accesso al valore Identity inserito.

INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

Set di risultati:

SCOPE_IDENTITY

29484

@@IDENTITY

89

Vedere anche

Riferimento

@@IDENTITY (Transact-SQL)