Qual é a diferença entre Scope_Identity (), Identity (), @@ Identity e Ident_Current ()?

Eu sei que Scope_Identity() , Identity() , @@Identity e Ident_Current() todos obtêm o valor da coluna de identidade, mas eu adoraria saber a diferença.

Parte da controvérsia que estou tendo é o que eles querem dizer com escopo aplicado a essas funções acima?

Eu também adoraria um exemplo simples de diferentes cenários de usá-los?

   
  • A function @@identity retorna a última identidade criada na mesma session.
  • A function scope_identity() retorna a última identidade criada na mesma session e no mesmo escopo.
  • O ident_current(name) retorna a última identidade criada para uma tabela ou exibição específica em qualquer session.
  • A function identity() não é usada para obter uma identidade, é usada para criar uma identidade em um select...into consulta.

A session é a conexão do database. O escopo é a consulta atual ou o procedimento armazenado atual.

Uma situação em que as scope_identity() e @@identity diferem, é se você tiver um triggersdor na tabela. Se você tiver uma consulta que insere um registro, fazendo com que o triggersdor insira outro registro em algum lugar, a function scope_identity() retornará a identidade criada pela consulta, enquanto a function @@identity retornará a identidade criada pelo triggersdor.

Então, normalmente você usaria a function scope_identity() .

Boa pergunta.

  • @@IDENTITY : retorna o último valor de identidade gerado em sua conexão SQL (SPID). Na maioria das vezes, será o que você quer, mas às vezes não é (como quando um gatilho é acionado em resposta a um INSERT , e o gatilho executa outra instrução INSERT ).

  • SCOPE_IDENTITY() : retorna o último valor de identidade gerado no escopo atual (ou seja, procedimento armazenado, gatilho, function, etc).

  • IDENT_CURRENT() : retorna o último valor de identidade para uma tabela específica. Não use isso para obter o valor de identidade de um INSERT , ele está sujeito a condições de corrida (ou seja, várias conexões inserindo linhas na mesma tabela).

  • IDENTITY() : usado ao declarar uma coluna em uma tabela como uma coluna de identidade.

Para mais referências, consulte: http://msdn.microsoft.com/pt-br/library/ms187342.aspx .

Para resumir: se você estiver inserindo linhas e quiser saber o valor da coluna de identidade da linha que acabou de inserir, sempre use SCOPE_IDENTITY() .

Se você entender a diferença entre o escopo e a session, será muito fácil entender esses methods.

Um post muito bacana de Adam Anderson descreve essa diferença:

Sessão significa a conexão atual que está executando o comando.

Escopo significa o contexto imediato de um comando. Cada chamada de procedimento armazenado é executada em seu próprio escopo e as chamadas aninhadas são executadas em um escopo nested no escopo do procedimento de chamada. Da mesma forma, um comando SQL executado a partir de um aplicativo ou SSMS é executado em seu próprio escopo e, se esse comando triggersr qualquer acionador, cada acionador será executado dentro de seu próprio escopo nested.

Assim, as diferenças entre os três methods de recuperação de identidade são as seguintes:

@@identity retorna o último valor de identidade gerado nesta session, mas qualquer escopo.

scope_identity() retorna o último valor de identidade gerado nesta session e neste escopo.

ident_current() retorna o último valor de identidade gerado para uma tabela específica em qualquer session e qualquer escopo.

Escopo significa o contexto de código que executa a instrução INSERT SCOPE_IDENTITY() , em oposição ao escopo global de @@IDENTITY .

 CREATE TABLE Foo( ID INT IDENTITY(1,1), Dummy VARCHAR(100) ) CREATE TABLE FooLog( ID INT IDENTITY(2,2), LogText VARCHAR(100) ) go CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS BEGIN INSERT INTO FooLog (LogText) VALUES ('inserted Foo') INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted END INSERT INTO Foo (Dummy) VALUES ('x') SELECT SCOPE_IDENTITY(), @@IDENTITY 

Dá resultados diferentes.

Para esclarecer o problema com @@Identity :

Por exemplo, se você inserir uma tabela e essa tabela tiver gatilhos fazendo inserções, @@Identity retornará o id da inserção no gatilho (um log_id ou algo assim), enquanto scope_identity() retornará o id da inserção no original mesa.

Portanto, se você não tiver gatilhos, scope_identity() e @@identity retornarão o mesmo valor. Se você tem gatilhos, precisa pensar em qual valor você gostaria.

Por causa do bug mencionado por @ David Freitas e por causa da incompatibilidade com o novo recurso Sequence que foi introduzido em 2012, eu recomendaria ficar longe de todos os três. Em vez disso, você pode usar a cláusula OUTPUT para obter o valor de identidade inserido. A outra vantagem é que o OUTPUT funciona mesmo se você tiver inserido mais de uma linha.

Para detalhes e exemplos veja aqui: Crise de Identidade

Aqui está outra boa explicação do livro :

Quanto à diferença entre SCOPE_IDENTITY e @@ IDENTITY, suponha que você tenha um procedimento armazenado P1 com três instruções:
– Um INSERT que gera um novo valor de identidade
– Uma chamada para um procedimento armazenado P2 que também possui uma instrução INSERT que gera um novo valor de identidade
– Uma declaração que consulta as funções SCOPE_IDENTITY e @@ IDENTITY A function SCOPE_IDENTITY retornará o valor gerado por P1 (mesma session e escopo). A function @@ IDENTITY retornará o valor gerado por P2 (mesma session, independentemente do escopo).

Scope Identity : Identidade do último registro adicionado dentro do procedimento armazenado que está sendo executado.

@@Identity : Identidade do último registro adicionado no lote de consulta, ou como resultado da consulta, por exemplo, um procedimento que realiza uma inserção; o acionador então aciona um acionador que insere um registro e retornará a identidade do registro inserido do acionador. .

IdentCurrent : a última identidade alocada para a tabela.