SCOPE_IDENTITY (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Retorna o último valor de identidade inserido em uma coluna de identidade no mesmo escopo. Um escopo é um módulo: um procedimento armazenado, gatilho, função ou lote. Portanto, se duas instruções forem estar no mesmo procedimento armazenado, função ou lote, elas estarão no mesmo escopo.

Convenções de sintaxe de Transact-SQL

Sintaxe

SCOPE_IDENTITY()  

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Tipos de retorno

numeric(38,0)

Comentários

SCOPE_IDENTITY, IDENT_CURRENT, e @@IDENTITY são funções semelhantes porque retornam valores que são inseridos em colunas de identidade.

IDENT_CURRENT não é limitado por escopo e sessão, mas a uma tabela especificada. IDENT_CURRENT retorna o valor gerado para uma tabela específica em qualquer sessão e escopo. Para obter mais informações, confira IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY e @@IDENTITY retornam o último valor de identidade gerado em qualquer tabela da sessão atual. Entretanto, SCOPE_IDENTITY só retorna valores inseridos no escopo atual; @@IDENTITY não é limitada a um escopo específico.

Por exemplo, há duas tabelas, T1 e T2e um gatilho INSERT é definido em T1. Quando uma linha é inserida em T1, o gatilho é acionado e insere uma linha em T2. Esse cenário ilustra dois escopos: a inserção em T1e a inserção em T2 pelo gatilho.

Supondo que T1 e T2 tenham colunas de identidade, @@IDENTITY e SCOPE_IDENTITY retornarão valores diferentes no fim de uma instrução INSERT em T1. @@IDENTITY retorna o último valor de coluna de identidade inserido em qualquer escopo na sessão atual. É o valor inserido em T2. SCOPE_IDENTITY() retorna o valor IDENTITY inserido em T1. Foi a última inserção que ocorreu no mesmo escopo. A função SCOPE_IDENTITY() retornará o valor nulo se for invocada antes que qualquer instrução INSERT em uma coluna de identidade ocorra no escopo.

Instruções e transações com falha podem alterar a identidade atual de uma tabela e criar lacunas nos valores da coluna de identidade. O valor de identidade nunca é revertido, mesmo que a transação que tentou inserir o valor na tabela não seja confirmada. Por exemplo, se uma instrução INSERT falhar por causa de uma violação IGNORE_DUP_KEY, o valor de identidade atual para a tabela ainda será incrementado.

Exemplos

a. Usando @@IDENTITY e SCOPE_IDENTITY com gatilhos

O exemplo a seguir cria duas tabelas, TZ e TY, e um gatilho INSERT em TZ. Quando uma linha é inserida na tabela TZ, o gatilho (Ztrig) é acionado e insere uma linha em 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;  

Conjunto de resultados: esta é a aparência da tabela 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;  

Conjunto de resultados: esta é a aparência da TY:

Y_id  Y_name  
---------------  
100   boathouse  
105   rocks  
110   elevator  

Crie o gatilho que insere uma linha na tabela TY quando uma linha é inserida na tabela TZ.

CREATE TRIGGER Ztrig  
ON TZ  
FOR INSERT AS   
   BEGIN  
   INSERT TY VALUES ('')  
   END;  

Dispara o gatilho com FIRE e determina quais valores de identidade você obtém com as funções @@IDENTITY e SCOPE_IDENTITY.

INSERT TZ VALUES ('Rosalie');  
  
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  
GO  
SELECT @@IDENTITY AS [@@IDENTITY];  
GO  

Este é o conjunto de resultados.

/*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. Usando @@IDENTITY e SCOPE_IDENTITY() com replicação

Os exemplos a seguir mostram como usar @@IDENTITY e SCOPE_IDENTITY() para inserções em um banco de dados publicado para replicação de mesclagem. As duas tabelas dos exemplos estão no banco de dados de exemplo AdventureWorks2022: Person.ContactType não é publicado e Sales.Customer é publicado. A replicação de mesclagem adiciona gatilhos a tabelas que são publicadas. Portanto, @@IDENTITY pode retornar o valor da inserção em uma tabela do sistema de replicação em vez da inserção em uma tabela de usuário.

A tabela Person.ContactType tem um valor de identidade máximo de 20. Se você inserir uma linha na tabela, @@IDENTITY e SCOPE_IDENTITY() retornarão o mesmo valor.

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  

Este é o conjunto de resultados.

SCOPE_IDENTITY  
21  
@@IDENTITY  
21

A tabela Sales.Customer tem um valor de identidade máximo de 29483. Se você inserir uma linha na tabela, @@IDENTITY e SCOPE_IDENTITY() retornarão valores diferentes. SCOPE_IDENTITY() retorna o valor da inserção em uma tabela de usuário, enquanto @@IDENTITY retorna o valor da inserção na tabela do sistema de replicação. Use SCOPE_IDENTITY() para aplicativos que requerem acesso ao valor de identidade inserido.

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

Este é o conjunto de resultados.

SCOPE_IDENTITY  
29484  
@@IDENTITY  
89

Consulte Também

@@IDENTITY (Transact-SQL)