@@ IDENTITY, SCOPE_IDENTITY (), OUTPUT and other methods for obtaining the latest identity

Question

I saw various methods used to extract the value of the primary key identification field after insertion.

declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@IDENTITY --returns 1 

Returning the identity table after insertion:

 Create Table #Testing ( id int identity, somedate datetime default getdate() ) insert into #Testing output inserted.* default values 

Which method is right or better? Is the OUTPUT method safe?

The second code snippet was borrowed from SQL in the Wild

Answer 1

It depends on what you are trying to do ...

@@ IDENTITY

Returns the last IDENTITY value created in the join, regardless of the table that produced the value, and regardless of the scope of the statement that produced this value. @@ IDENTITY will return the last identifier value entered in the table of the current session. @@ IDENTITY is limited to the current session and not limited to the current area. For example, if you have a trigger in a table that forces an individual to be created in another table, you will get the identifier that was created last, even if it was the trigger that created it.

SCOPE_IDENTITY ()

Returns the last IDENTITY value created in the join and by the operator in the same scope, regardless of the table that generated the value. SCOPE_IDENTITY () is similar to @@ IDENTITY, but it also limits the value of the current scope. In other words, it will return the last identifier value that you explicitly created, and not any identity created by a trigger or user-defined function.

IDENT_CURRENT ()

Returns the last IDENTITY value created in the table, regardless of the connection and scope of the statement that generated the value. IDENT_CURRENT is limited to the specified table, but not to a join or scope.

Answer 2

Please note that scope_identity() and @@identity have an error - see MS Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

Quote (from Microsoft):

I highly recommend using OUTPUT instead of @@IDENTITY in all cases. This is simply the best way to read your personality and time stamp.

Edited to add: this can be fixed now. Connect gives an error, but see:

Scope_Identity () returns wrong value fixed?

Answer 3

It makes little sense to use anything other than an OUTPUT clause when trying to get the identifier of the rows just inserted. The OUTPUT offer is safe and secure.

Here is a simple example of getting an identifier after inserting one row ...

 DECLARE @Inserted AS TABLE (MyTableId INT); INSERT [MyTable] (MyTableColOne, MyTableColTwo) OUTPUT Inserted.MyTableId INTO @Inserted VALUES ('Val1','Val2') SELECT MyTableId FROM @Inserted 

Detailed documents for the OUTPUT proposal: http://technet.microsoft.com/en-us/library/ms177564.aspx

---

 -- table structure for example: CREATE TABLE MyTable ( MyTableId int NOT NULL IDENTITY (1, 1), MyTableColOne varchar(50) NOT NULL, MyTableColTwo varchar(50) NOT NULL ) 

Answer 4

@@ Identity is an old school. Use SCOPE_IDENTITY () in all cases in the future. See MSDN for the implications of using @@ IDENTITY (they are bad!).

Answer 5

SCOPE_IDENTITY is sufficient for individual lines and is recommended, unless for some reason you need to see the result of an intermediate TRIGGER (why?).

For multiple rows, OUTPUT / OUTPUT INTO is your new best friend and an alternative to re-setting rows and pasting into another table.

Answer 6

In SQL Server 2005 there is another method in which in SQL in Wild .

This will allow you to get multiple identifiers after insertion. Here is the code from the blog post:

 Create Table #Testing ( id int identity, somedate datetime default getdate() ) insert into #Testing output inserted.* default values 

Answer 7

A small amendment to Godeck:

This is not just a concern. Any nested operation, such as stored procedures that creates identifiers, can change @@ IDENTITY.

Another vote for scope_identity ...

Answer 8

Be helpful when using @@ IDENTITY ...

http://dotnetgalactics.wordpress.com/2009/10/28/scope-identity-vs-identity/