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.
Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Windows Azure (versão inicial até a versão atual). |
.gif "Ícone de vínculo de tópico")
Convenções da sintaxe Transact-SQL
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 é disparado 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 tanto T1 quanto T2 possuem colunas de identidade, @@IDENTITY e SCOPE_IDENTITY retornarão valores diferentes ao final de uma instrução INSERT em T1. @@IDENTITY retornará o último valor de coluna de identidade inserido em qualquer escopo na sessão atual. Este é 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'),('Mike'),('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'), ('rocks'), ('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 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.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 AdventureWorks2012 : 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 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
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 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
Aqui está o conjunto de resultados.
SCOPE_IDENTITY
29484
@@IDENTITY
89