@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

Question

I have seen various methods used when retrieving the value of a primary key identity field after insert.

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 a table of identities following insert:

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

What method is proper or better? Is the OUTPUT method scope-safe?

The second code snippet was borrowed from SQL in the Wild

Solution 1

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

@@IDENTITY

Returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. @@IDENTITY is limited to the current session and is not limited to the current scope. For example, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SCOPE_IDENTITY()

Returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY() is similar to @@IDENTITY, but it will also limit the value to your current scope. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

IDENT_CURRENT()

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

Solution 2

Note that there is a bug in scope_identity() and @@identity - see MS Connect: https://web.archive.org/web/20130412223343/https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

A quote (from Microsoft):

I highly recommend using OUTPUT instead of @@IDENTITY in all cases. It's just the best way there is to read identity and timestamp.

Edited to add: this may be fixed now. Connect is giving me an error, but see:

Scope_Identity() returning incorrect value fixed?

Solution 3

There is almost no reason to use anything besides an OUTPUT clause when trying to get the identity of the row(s) just inserted. The OUTPUT clause is scope and table safe.

Here's a simple example of getting the id after inserting a single row...

DECLARE @Inserted AS TABLE (MyTableId INT);

INSERT [MyTable] (MyTableColOne, MyTableColTwo)
OUTPUT Inserted.MyTableId INTO @Inserted
VALUES ('Val1','Val2')

SELECT MyTableId FROM @Inserted

Detailed docs for OUTPUT clause: 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
)

Solution 4

@@Identity is the old school way. Use SCOPE_IDENTITY() in all instances going forward. See MSDN for the repercussions of using @@IDENTITY (they're bad!).

Solution 5

SCOPE_IDENTITY is sufficient for single rows and is recommended except in cases where you need to see the result of an intermediate TRIGGER for some reason (why?).

For multiple rows, OUTPUT/OUTPUT INTO is your new best friend and alternative to re-finding the rows and inserting into another table.

View more solutions

Comments

[jcollum]

I've seen this before, but assumed it was not scope-safe. Meaning I might see someone else's inserts. I don't know if that's true.

[Seibar]

I am not sure. I will add this to the question.

[MatBailie]

Even SCOPE_IDENTITY() has problems with triggers which do their own INSERTs. (Giving you the identity of the last table any triggers inserte in to)

[jcollum]

There's sample code in that link that seems to contradict your statement. Pretty sure that triggers happen in a different scope.

[Haydar]

I just wanted to mention that the bug above occurs when parallelism is used. But this wouldn't effect cases where a single insert is performed. Heres a quote from Microsoft. "whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can't be relied upon."

[dburges]

@Dems, that is flat out not true

[dburges]

Output is scope safe. It is the preferred method from now on.

[dburges]

You did not describe using OUTPUT which is the preferred method in newere versions of SQL Server. Not only is it scope safe, but it can return multiple identities and even other fields if you need them for futre processing.

[MatBailie]

Wow, a response to a comment made two years ago... And yes, it appears that I was either drunk or just extremely dumb the day I added that comment.

[Alex S]

@HLGEM, OUTPUT is nice, but a bit awkward to use when you need just a single identity value.

[dburges]

@Constantin, while that is true, it is Microsoft's preferred way to get the value and from the things I've read they have no intention of fixing any problesm with @@identity and scope_Identity in the future as they consider OUTPUT to be the replacement.

[Admin]

What about SCOPE_IDENTITY()? It seems like @@IDENTITY has always been problematic for more use-cases.

[gotqn]

@HLGEM Where it is said that @@identity will be replaced by OUTPUT?

[JeremyWeir]

Note: when using this type of thing in a cursor, be sure to delete all from the table variable in each iteration since DECLARE does not reinitialize.

[Stefan Steiger]

@HLGEM: Note that output breaks if I have an insert-trigger on a table (e.g. history-trigger). You need to output into a table-variable, and then select from that variable.

[Domcha]

@stefan-steiger Sorry but are you sure that the output is break by insert-trigger ? From MS doc: "Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed" Thanks

[Stefan Steiger]

@Domcha: Yes, it tells you so when you attempt to run output when you have an insert-trigger.