65

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

gotqn
  • 42,737
  • 46
  • 157
  • 243
Seibar
  • 68,705
  • 38
  • 88
  • 99

8 Answers8

76

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.

mson
  • 7,762
  • 6
  • 40
  • 70
  • 24
    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. – HLGEM Nov 19 '10 at 19:42
  • 4
    @HLGEM, OUTPUT is nice, but a bit awkward to use when you need just a single identity value. – Constantin Dec 13 '10 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. – HLGEM Dec 13 '10 at 17:01
  • 4
    @HLGEM Where it is said that @@identity will be replaced by OUTPUT? – gotqn Sep 12 '13 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. – Stefan Steiger Apr 10 '17 at 15:01
  • @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 – Domcha Nov 27 '19 at 11:56
  • 1
    @Domcha: Yes, it tells you so when you attempt to run output when you have an insert-trigger. – Stefan Steiger Nov 27 '19 at 16:54
15

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?

Jake Braun
  • 1,172
  • 1
  • 13
  • 34
Simon D
  • 4,150
  • 5
  • 39
  • 47
  • 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." – Haydar Oct 02 '09 at 14:19
  • What about SCOPE_IDENTITY()? It seems like @@IDENTITY has always been problematic for more use-cases. –  Jan 04 '11 at 20:44
12

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
)
JeremyWeir
  • 24,118
  • 10
  • 92
  • 107
  • 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. – JeremyWeir Feb 26 '15 at 18:02
6

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

jcollum
  • 43,623
  • 55
  • 191
  • 321
  • 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) – MatBailie Jan 27 '09 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. – jcollum Jan 28 '09 at 18:20
  • 2
    @Dems, that is flat out not true – HLGEM Nov 19 '10 at 19:39
  • 1
    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. – MatBailie Dec 03 '10 at 15:41
4

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
3

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
Seibar
  • 68,705
  • 38
  • 88
  • 99
3

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.

Another vote for scope_identity...

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
1

Be carreful while using @@IDENTITY ...

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

Pato
  • 161
  • 1
  • 4