Melhor maneira de obter a última identidade inserida em uma tabela

UseSCOPE_IDENTITY() se estiver inserindo uma única linha e quiser recuperar o ID que foi gerado.

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

Resultado:

----
1

Use a OUTPUTcláusula se estiver inserindo várias linhas e precisar recuperar o conjunto de IDs que foram gerados.

INSERT #a(x) 
  OUTPUT inserted.identity_column 
  VALUES('b'),('c');

Resultado:

----
2
3

e por que esta é a melhor opção mais rápida?

Além do desempenho, esses são os únicos que garantem estar corretos no nível de isolamento padrão e/ou com vários usuários. Mesmo se você ignorar o aspecto de correção, o SQL Server mantém o valor inserido SCOPE_IDENTITY()na memória, portanto, naturalmente, isso será mais rápido do que executar sua própria consulta isolada na tabela ou nas tabelas do sistema.

Ignorar o aspecto de correção é como dizer ao carteiro que ele fez um bom trabalho entregando a correspondência de hoje - ele terminou sua rota 10 minutos mais rápido que seu tempo médio, o problema é que nenhuma correspondência foi entregue na casa certa.

Não use nenhum dos seguintes:

• @@IDENTITY- como isso não pode ser usado em todos os cenários, por exemplo, quando uma tabela com uma coluna de identidade tem um gatilho que também insere em outra tabela com sua própria coluna de identidade - você receberá o valor errado de volta.
• IDENT_CURRENT()- Eu entro em detalhes sobre isso aqui , e os comentários são uma leitura útil também, mas essencialmente, em concorrência, você muitas vezes receberá a resposta errada.
• MAX()ou TOP 1- você teria que proteger as duas instruções com isolamento serializável para garantir que o que MAX()você obtém não seja de outra pessoa. Isso é muito mais caro do que apenas usar SCOPE_IDENTITY().

Essas funções também falham sempre que você insere duas ou mais linhas, e precisa de todos os valores de identidade gerados - sua única opção aí é a OUTPUTcláusula.

Além do desempenho, todos eles têm significados bastante diferentes.

SCOPE_IDENTITY()fornecerá o último valor de identidade inserido em qualquer tabela diretamente no escopo atual (escopo = lote, procedimento armazenado etc., mas não dentro, digamos, de um gatilho que foi disparado pelo escopo atual).

IDENT_CURRENT()fornecerá o último valor de identidade inserido em uma tabela específica de qualquer escopo, por qualquer usuário.

@@IDENTITYfornece o último valor de identidade gerado pela instrução INSERT mais recente para a conexão atual, independentemente da tabela ou do escopo. (Observação: o Access usa essa função e, portanto, tem alguns problemas com gatilhos que inserem valores em tabelas com colunas de identidade.)

Usar MAX()ou TOP 1pode fornecer resultados totalmente errados se a tabela tiver uma etapa de identidade negativa ou tiver linhas inseridas SET IDENTITY_INSERTem jogo. Aqui está um script demonstrando tudo isso:

CREATE TABLE ReverseIdent (
    id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
    data char(4)
)

INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')

SELECT * FROM ReverseIdent

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000

SET IDENTITY_INSERT ReverseIdent ON

INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')

SET IDENTITY_INSERT ReverseIdent OFF

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005

Resumo: fique com SCOPE_IDENTITY(), IDENT_CURRENT(), ou @@IDENTITY, e certifique-se de estar usando aquele que retorna o que você realmente precisa.