Visual Basic, VB .NET, ASP, ActiveX, Access, SQL Server

SQL Server: obtendo o identificador errado com @@Identity

Se você tem trabalhado com outros bancos de dados, os métodos para obter valores de identidade no SQL Server podem não parecer diretos. Em alguns bancos de dados, o seu campo de auto-numeração é atribuído no início da inclusão. No Oracle,  se você usa uma sequência, você tem o valor de identidade antes de iniciar a inclusão. Com o SQL Server, a nova identidade não é conhecida até que a inclusão se complete. Isto leva a problemas quando você precisa da nova identidade para continuar com uma transação diferente ou retornar o valor para a sua aplicação para uso futuro.

Há três métodos para obter a nova identidade. A diferença entre eles está no escopo e sessão a que se referem. Usando o método incorreto você obterá o valor errado para a identidade.

SCOPE_IDENTITY

A função SCOPE_IDENTITY retorna a nova identidade criada em qualquer tabela nesta sessão e neste escopo, dando-nos o novo valor que esperamos obter. No exemplo abaixo, um novo produto será introduzido e terá a próxima identidade igual a 54.

INSERT INTO produtos
(nome_produto)
VALUES
('dvd')

SELECT SCOPE_IDENTITY() 


Apenas para confirmar: 

SELECT * FROM Produtos where nome_produto = 'dvd'



@@IDENTITY

Esta variável global é habitualmente a primeira sugerida nos livros e grupos de discussão para a descoberta de novos valores de identidade. Contudo, o seu propósito real não é tão evidente e pode produzir o valor errado de identidade. @@IDENTITY retornará a última identidade usada na sessão, mas em todos os escopos. Isto quer dizer que, se a sua inclusão disparar um trigger que também faz uma inclusão em uma tabela com um campo identity,  a identidade gerada pelo trigger será retornada, não aquela gerada pela sua inclusão. Por exemplo, crie uma tabela de teste com um campo identity e um trigger na tabela Produtos que inserirá algo na tabela de teste. 

CREATE TABLE teste(id int IDENTITY(100,1))
GO
CREATE TRIGGER trigger1 ON produtos FOR INSERT 
AS
BEGIN
INSERT teste DEFAULT VALUES
END
GO


Agora,  quando uma inclusão for feita na tabela Produtos, o trigger irá causar uma inclusão na tabela Teste. Em seguida, nós obtemos a nova identidade usando ambos os métodos discutidos acima.


INSERT INTO produtos
(nome_produto)
VALUES
('tvs')

SELECT @@IDENTITY 
--retorna 100, o valor de identidade da tabela Teste causado pelo trigger

SELECT SCOPE_IDENTITY()
--retorna 56, da sua consulta de inclusão na tabela produtos

Os diferentes valores retornados se devem à diferença de escopos examinados por cada método. Se você quiser a identidade gerada pela sua ação mais recente, use SCOPE_IDENTITY(). Se você quiser a identidade criada por qualquer outra ação decorrente da sua, use @@IDENTITY. 

O terceiro método usa a função IDENT_CURRENT. IDENT_CURRENT requer um parâmetro para o nome da tabela. Como @@IDENTITY, ela examinará todos os escopos, mas em adição, ela examinará todas as sessões, não apenas a sua.

Conclusão

O uso de campos de identidade no SQL Server é  um modo rápido e eficiente de gerar valores únicos. Se o método correto for usado para obter as novas identidades, a sua aplicação estará garantida contra os perigos de obter a identidade errada.