What’s the difference and benefits of using @@IDENTITY and OUTPUT INSERTED.ID

Asked

Viewed 1,709 times

6

I’ve always used the @@IDENTITY to get the last inserted identity value, 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 the OUTPUT INSERTED.ID.

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

sql-server return identity-server

1 answer

8


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

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

Below each one explored in a brief way.

@@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 watch out when using it.

SCOPE_IDENTITY()

Returns the last ID generated within the current scope. The current scope may be a stored Procedure, Trigger or an explicit INSERT given by the user. This is a more guaranteed way to know the last ID generated than @@IDENTITY, since it avoids Ids that may have been generated by triggers executed indirectly.

IDENT_CURRENT()

This function returns the last ID generated for the table passed as parameter. Be careful, because there are people who have 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 from SQL Server 2005). To get the last record with it just use OUTPUT INSERTED.ID. As exemplified here /a/99820/3084

It is very powerful, because in addition to allowing you to know the last ID inserted, it allows information from the included record to be inserted into another table, all with a very clear and streamlined 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 a record is being inserted into the table User and in OUTPUT itself another is inserted in the table User_log.

SELECT MAX

This method consists of performing a SELECT MAX(CAMPO_ID) FROM TABLE. It is not indicated when inserting a record, because it takes an extra SQL to know information that can be obtained from the forms shown above. Also, if the value of the ID field is not an Integer (for example, a GUID), then this method will fail.

Browser other questions tagged sql-server return identity-server

You are not signed in. Login or sign up in order to post.