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

Asked 26/1, 2009 at 21:19 Answered 8/12, 2014 at 18:53

sql sql-server t-sql identity output-clause

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

Build answered 26/1, 2009 at 21:19 Comment(0)

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.

Nolitta answered 26/1, 2009 at 21:28 Comment(7)

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. – Slavism 19/11, 2010 at 19:42

@HLGEM, OUTPUT is nice, but a bit awkward to use when you need just a single identity value. – Pudency 13/12, 2010 at 16:55

@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. – Slavism 13/12, 2010 at 17:1

@Slavism Where it is said that @@identity will be replaced by OUTPUT? – Printery 12/9, 2013 at 10:20

@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. – Photogene 10/4, 2017 at 15:1

@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 – Saucier 27/11, 2019 at 11:56

@Domcha: Yes, it tells you so when you attempt to run output when you have an insert-trigger. – Photogene 27/11, 2019 at 16:54

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?

Postexilian answered 15/9, 2009 at 14:47 Comment(2)

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." – Notional 2/10, 2009 at 14:19

What about SCOPE_IDENTITY()? It seems like @@IDENTITY has always been problematic for more use-cases. – Assent 4/1, 2011 at 20:44

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
)

Radiobroadcast answered 8/12, 2014 at 18:53 Comment(2)

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. – Radiobroadcast 26/2, 2015 at 18:2

Unfortunately I guess there might be a pretty good reason: https://mcmap.net/q/46357/-cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table – Cere 19/5 at 11:0

@@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!).

Cupid answered 26/1, 2009 at 21:22 Comment(4)

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) – Their 27/1, 2009 at 22:14

There's sample code in that link that seems to contradict your statement. Pretty sure that triggers happen in a different scope. – Cupid 28/1, 2009 at 18:20

@Dems, that is flat out not true – Slavism 19/11, 2010 at 19:39

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. – Their 3/12, 2010 at 15:41

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.

Revalue answered 26/1, 2009 at 22:25 Comment(0)

There is another method available in SQL Server 2005 that is outlined in SQL in the Wild.

This will allow you to retrieve multiple identities after insert. Here's the code from the blog post:

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

Build answered 26/1, 2009 at 21:22 Comment(3)

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. – Cupid 26/1, 2009 at 21:25

I am not sure. I will add this to the question. – Build 26/1, 2009 at 21:28

Output is scope safe. It is the preferred method from now on. – Slavism 19/11, 2010 at 19:40

A small correction to Godeke's answer:

It's not just triggers you need to worry about. Any kind of nested operation, such as stored procs, that causes identifiers to be created could change the value of @@IDENTITY.

Recommended topics

Hot tags