SCOPE_IDENTITY (Transact-SQL)
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, duas instruções estarão no mesmo escopo se eles estiverem no mesmo procedimento armazenado, função ou lote.
.gif "Ícone de vínculo de tópico")
Sintaxe
SCOPE_IDENTITY()
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, consulte 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 T2, e 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 T1 e 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 retornará o último valor de coluna de identidade inserido por qualquer escopo na sessão atual. É o valor inserido em T2. SCOPE_IDENTITY() retornará 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')
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
Aqui está o conjunto de resultados.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY retornou o último valor de identidade no mesmo escopo. Essa era a inserção na tabela TZ.*/
@@IDENTITY
115
/*@@IDENTITY retornou o último valor de identidade inserido em TY pelo gatilho. Este foi disparado devido a uma inserção anterior em TZ.*/
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 AdventureWorks2008R2: 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 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
Aqui está 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 na 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
Aqui está o conjunto de resultados.
SCOPE_IDENTITY
29484
@@IDENTITY
89