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

Question

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

Navigation

#1 by (8 votes)

6

I've 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 a difference between the two in terms of performance or some difference in usage?

sql-server return identity-server

asked by anonymous 24.11.2015 / 16:52

1 answer

List of project directories in Laravel is displayed instead of running the application [duplicate] How to make a loop that compares string with a float in python?

score 8 · Accepted Answer

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

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

Below each one explored briefly.

@@ 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 has been 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 of knowing the last ID generated than @@ IDENTITY, since it avoids IDs that have eventually been generated by indirectly executed triggers.

IDENT_CURRENT ()

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

For the last time, NO, you can not 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 link

It is very powerful because it allows you to know the last inserted ID, it allows the information of the included record to be inserted in another table, all with a clear and clear syntax. See an example taken from link

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 User table and in the OUTPUT itself another one is inserted into the User_Log table.

SELECT MAX

This method consists of performing a SELECT MAX (CAMPO_ID) FROM TABLE. It is not indicated when you are inserting a record, because you spend an extra SQL to know 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.