What is the difference and advantages in using @ @ IDENTITY and OUTPUT INSERTED.ID

Question

What is the difference and advantages in using @ @ IDENTITY and OUTPUT INSERTED.ID

I always used @@IDENTITY to get the last identity value entered, as in the example below.

create Proc [dbo].[Arquivos]
  @IdArquivo int,
  @IdArquivo_Out int output
as
begin
  Set Xact_Abort on
  Set Nocount on
  Begin Tran

  begin
    Insert into tb_Arquivos (IdArquivo, DtInclusao)
    values (@IdArquivo, GETDATE()
     )
    Set @IdArquivo_Out = @@IDENTITY
  Commit
end

But seeing this answer these days I was in doubt with the use of OUTPUT INSERTED.ID.

Is there any difference between the two in terms of performance or any difference for use?

6

return sql-server identity-server

Author: Comunidade, 2015-11-24

Source

1 answers

score 8 · Accepted Answer

There are 5 methods to get the last ID entered, they are:

@ @ IDENTITY
SCOPE_IDENTITY ()
IDENT_CURRENT ('table name here')
output
SELECT MAX

Below each briefly explored.

@ @ IDENTITY

Returns the last ID generated in the current user session. This ID may have been generated by an explicit insert command given by the user or indirectly by a trigger that was executed within the same session. You have to be careful when using it.

SCOPE_IDENTITY ()

Returns the last ID generated within the current scope. The current scope can be a stored procedure, trigger, or an explicit INSERT that has been given by the user. This is a more guaranteed way to know the last generated ID than @@IDENTITY, since it avoids IDs that were eventually generated by indirectly executed triggers.

IDENT_CURRENT ()

This function returns the last generated ID for the table passed as a parameter. Be careful, because there are people who have already written saying that this method is not transaction-safe .

For the last time, NO, you can't trust IDENT_CURRENT()

Output

The output method is relatively recent (I believe as of SQL Server 2005). To get the last record with it just use OUTPUT INSERTED.ID . as exemplified here https://pt.stackoverflow.com/a/99820/3084

It is very powerful, because in addition to allowing to know the last ID entered, it allows information from the included record to be entered in another table, all with a lean and very clear syntax. See an example taken from https://stackoverflow.com/a/26400584/2236741

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
VALUES (1, '[email protected]'), (2, '[email protected]');

Note that one record is being inserted into the table user and the output itself inserts another into the table User_Log .

SELECT MAX

This method consists of performing a select MAX (CAMPO_ID) from table. It is not indicated when you are entering a record, because you spend an extra SQL to know an information that can be obtained in the ways shown above. Also, if the value of the ID field is not an integer (a GUID, for example), then this method will fail.