By Oded


2008-09-03 21:32:02 8 Comments

What is the best way to get IDENTITY of inserted row?

I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each.

Can someone please explain the differences and when I should be using each?

13 comments

@Frank Roth 2019-03-13 12:55:06

Create a uuid and also insert it to a column. Then you can easily identify your row with the uuid.

@Sparky 2019-01-04 18:49:33

If you are looking for the last ID added/updated, this may be a little old-school, but there are a lot of people using older PHP, Pre 5.5 to be more precise. More details can be found at http://php.net/manual/en/function.mysql-insert-id.php

$last = mysql_insert_id();

@MarredCheese 2018-06-06 16:58:15

I can't speak to other versions of SQL Server, but in 2012, outputting directly works just fine. You don't need to bother with a temporary table.

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES (...)

By the way, this technique also works when inserting multiple rows.

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES
    (...),
    (...),
    (...)

Output

ID
2
3
4

@JohnOsborne 2018-11-05 20:39:34

If you want to use it later though, I imagine you need the temp table

@MarredCheese 2018-11-05 23:33:10

@JohnOsborne You are welcome to use a temp table if you like, but my point was that it's not a requirement of OUTPUT. If you don't need the temp table, then your query ends up being much simpler.

@Khan Ataur Rahman 2017-12-31 06:04:43

After Your Insert Statement you need to add this. And Make sure about the table name where data is inserting.You will get current row no where row affected just now by your insert statement.

IDENT_CURRENT('tableName')

@TT. 2017-12-31 07:53:14

Did you notice this exact same suggestion has been answered several times before?

@Khan Ataur Rahman 2017-12-31 10:09:26

yes. but i am trying to describe the solution in my own way.

@Trondster 2018-03-09 13:50:46

And if someone else have inserted a row in between your insert statement and your IDENT_CURRENT() call, you'll get the id of the record someone else have inserted - probably not what you want. As noted in most of the replies above - in most cases you should rather use SCOPE_IDENTITY().

@Ian Kemp 2013-04-29 07:22:55

The best (read: safest) way to get the identity of a newly-inserted row is by using the output clause:

create table TableWithIdentity
           ( IdentityColumnName int identity(1, 1) not null primary key,
             ... )

-- type of this table's column must match the type of the
-- identity column of the table you'll be inserting into
declare @IdentityOutput table ( ID int )

insert TableWithIdentity
     ( ... )
output inserted.IdentityColumnName into @IdentityOutput
values
     ( ... )

select @IdentityValue = (select ID from @IdentityOutput)

@Martin Smith 2017-01-07 18:51:04

SQL server clustering is a high availability feature and has no bearing on parallelism. It is very uncommon for single row inserts (the most common case for scope_identity()) to get parallel plans anyway. And this bug was fixed more than a year before this answer.

@user1451111 2017-11-06 08:47:34

What do you mean by parallelism.

@Ian Kemp 2017-11-06 12:26:48

@MartinSmith The client wasn't willing to allow downtime on their server cluster to install the CU fixing this issue (not joking), so the only solution was for us to rewrite all the SQL to use output instead of scope_identity(). I have removed the FUD about clustering in the answer.

@Sean Ray 2018-03-21 14:50:51

Thank you, this is the only example I've been able to find that shows how to use the value from the output in a variable instead of just outputting it.

@Guy Starbuck 2008-09-03 21:42:55

@@IDENTITY is the last identity inserted using the current SQL Connection. This is a good value to return from an insert stored procedure, where you just need the identity inserted for your new record, and don't care if more rows were added afterward.

SCOPE_IDENTITY is the last identity inserted using the current SQL Connection, and in the current scope -- that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed. Frankly, I have never had a reason to use this.

IDENT_CURRENT(tablename) is the last identity inserted regardless of connection or scope. You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

@HLGEM 2011-05-20 14:51:57

You should never use @@identity for this purpose. If someone adds a trigger later, you will lose data integrity. @@identiy is an extremely dangerous practice.

@Abdul Saboor 2013-02-26 10:07:41

"value for a table that you have <<not>> inserted a record into." Really?

@Ian Boyd 2016-11-04 15:05:48

When you use Entity Framework, it internally uses the OUTPUT technique to return the newly inserted ID value

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID INTO @generated_keys
VALUES('Malleable logarithmic casing');

SELECT t.[TurboEncabulatorID ]
FROM @generated_keys AS g 
   JOIN dbo.TurboEncabulators AS t 
   ON g.Id = t.TurboEncabulatorID 
WHERE @@ROWCOUNT > 0

The output results are stored in a temporary table variable, joined back to the table, and return the row value out of the table.

Note: I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).

But that's what EF does.

This technique (OUTPUT) is only available on SQL Server 2008 or newer.

@zaitsman 2017-11-23 03:30:17

i imagine they match them to ensure integrity (e.g. in optimistic concurrency mode, while you are selecting from the table variable, someone may have removed the inserter rows). Also, love your TurboEncabulators :)

@Jim 2015-03-30 18:25:01

Add

SELECT CAST(scope_identity() AS int);

to the end of your insert sql statement, then

NewId = command.ExecuteScalar()

will retrieve it.

@bdukes 2008-09-03 21:38:23

  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

  • SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

  • IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

  • The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

@KM. 2010-01-28 14:59:50

known bug with SCOPE_IDENTITY() returning the wrong values: blog.sqlauthority.com/2009/03/24/… the work around is to not run the INSERT in a Multi Processor Parallel Plan or use the OUTPUT clause

@HLGEM 2011-05-20 14:47:21

@@Identity is worse to use the Scope_identity. You should almost never use @@identity.

@HLGEM 2011-05-20 14:49:46

ident_current is extremely dangerous to data integrity. We had someone use that and simluatanous actions onthe database resulted on the child tables getting assigned to the wrong person. Almost imopossible to fix the data when that happens.

@HLGEM 2011-05-20 14:50:41

since this is the accepted answer, please add the output clause to your answer as this is the preferred way to do this in the newer versions fo SQL Server.

@bdukes 2011-05-20 15:12:42

@HLGEM Feel free to edit if you have anything to add/change. Anything you can point to indicating that it's the preferred way?

@Mitch Wheat 2012-02-12 01:52:47

@jerry 2012-02-15 15:48:37

Almost every time I have ever wanted 'identity', I have wanted to know the key(s) of the record(s) I just inserted. If that is your situation, you want to use the OUTPUT clause. If you want something else, apply the effort to read and understand bdukes response.

@paparazzo 2012-10-01 13:07:17

For another reason not to use @@Indentity is that in my experience it is O(n) and SCOPE_IDENTITY() is O(1). It only showed up on a really big table.

@spb 2014-01-06 19:04:45

With output you don't need to create a temp table to store and query the results. Just leave off the into part of the output clause and it will output them to a resultset.

@GaTechThomas 2014-09-08 17:09:19

To save others from panicing, the bug mentioned above was fixed in Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1.

@Robert Grant 2014-09-26 06:47:05

Why doesn't SCOPE_IDENTITY's limiting to the current scope make it safe to use?

@Jeeva Jsb 2015-09-28 08:39:53

Can we get identity from multiple insert?

@Michael L. 2016-08-30 19:31:04

Note that it seems you can't use OUTPUT when the two tables have an fkey relationship, unless you use an intermediary temp table (which has to be already defined before the INSERT statement).

@Miryafa 2018-02-05 15:09:18

Note this only works in SQL Server and not in MySQL (for anyone in my situation getting confused why it doesn't work)

@Alejandro 2018-05-14 20:29:32

@Miryafa Nobody mentioned MySql anywhere. The question was explicitly about SQL Server, no surprise that it doesn't works in any other DB engine.

@Alejandro 2018-05-14 20:33:45

The OUTPUT clause is also buggy in that it doesn't takes into account the execution of triggers and also has problems with timestamps. I had to change the return of values from OUTPUT back to SCOPE_IDENTITY in an extra SELECT precisely for this bug. See also: stackoverflow.com/a/13198916/2557263

@niico 2019-02-19 15:07:18

So update for 2019? Best to use Output Inserted.Id or Scope_Identity? What is best practice?

@bdukes 2019-02-26 19:07:24

@niico, I think the recommendation is the same as it has been, which is that OUTPUT is the "best" so long as you aren't using triggers and are handling errors, but SCOPE_IDENTITY is the simplest and very rarely has issues

@Frank Roth 2019-03-13 12:52:52

Pracmatically: Generate a uuid and insert it. Then you can easily identify your row.

@Orry 2011-05-20 14:43:54

I believe the safest and most accurate method of retrieving the inserted id would be using the output clause.

for example (taken from the following MSDN article)

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

@HLGEM 2011-05-20 14:48:26

Yes this is the correct method going forward, only use one of the others if you are not on SQL Server 2008 (we skipped 2005 so not sure if OUTPUT was available then)

@bdukes 2011-05-20 15:14:17

@HLGEM There's an MSDN page for OUTPUT in SQL Server 2005, so looks like it's just SQL Server 2000 and earlier that are without it

@SwissCoder 2011-07-28 17:40:53

woohoo! OUTPUT CLAUSE rocks :) That will simplify my current task. Didn't know that statement before. Thank you guys!

@Luke 2015-07-29 07:29:33

For a really concise example to just get the inserted ID, have a look at: stackoverflow.com/a/10999467/2003325

@shlgug 2016-12-01 17:22:23

Your use of INTO with OUTPUT is a good idea. See: blogs.msdn.microsoft.com/sqlprogrammability/2008/07/11/… (From a comment here: stackoverflow.com/questions/7917695/…)

@Yogurtu 2018-04-12 01:31:51

I Just learned about this OUTPUT INSERT feature, that looks like the real answer, works very well in sqlserver, but doesn't work with SqlClient class, it throws System.Data.SqlClient.SqlException: 'Cannot find either column "INSERTED" or the user-defined function or aggregate "INSERTED.Id", or the name is ambiguous.', I'm raising a question in other thread so if anybody knows the solution, will be appreciated: stackoverflow.com/questions/49786640/…

@Henrik Staun Poulsen 2019-03-26 09:19:17

Is it possible to get columns from the VALUES part (or SELECT if you use that)? The examples in MSDN shows that on the DELETE statement, but I cannot get it to work on SELECT/VALUES

@erikkallen 2009-10-09 20:35:00

ALWAYS use scope_identity(), there's NEVER a need for anything else.

@CJM 2009-10-09 20:44:25

Not quite never but 99 times out of 100, you'll use Scope_Identity().

@erikkallen 2009-10-09 21:31:14

For what have you ever used anything else?

@KM. 2010-01-28 14:48:22

if you insert several rows with an INSERT-SELECT, you would need to capture the multiple IDs using the OUTPUT clause

@erikkallen 2010-01-29 11:10:33

@KM: Yes, but I referred to scope_identity vs @@identity vs ident_current. OUTPUT is a completely different class and often useful.

@Dan B 2014-09-01 21:06:40

Check out Orry's ( stackoverflow.com/a/6073578/2440976) answer to this question - in parallelism, and just as a best practice, you would be wise to follow his setup... just brilliant!

@niico 2019-02-19 15:08:17

Why is it better than Output Inserted.Id?

@Orion Edwards 2008-09-03 21:44:50

I'm saying the same thing as the other guys, so everyone's correct, I'm just trying to make it more clear.

@@IDENTITY returns the id of the last thing that was inserted by your client's connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want

SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned. Hooray

IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.

If you want to play it safe, always use SCOPE_IDENTITY(). If you stick with @@IDENTITY and someone decides to add a trigger later on, all your code will break.

@Jakub Šturc 2008-09-03 21:37:02

MSDN

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

  • IDENT_CURRENT is a function which takes a table as a argument.
  • @@IDENTITY may return confusing result when you have an trigger on the table
  • SCOPE_IDENTITY is your hero most of the time.

Related Questions

Sponsored Content

10 Answered Questions

45 Answered Questions

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

20 Answered Questions

[SOLVED] Get size of all tables in database

  • 2011-10-25 16:14:34
  • Eric
  • 1091195 View
  • 1066 Score
  • 20 Answer
  • Tags:   sql-server tsql

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

37 Answered Questions

[SOLVED] How can I remove duplicate rows?

16 Answered Questions

[SOLVED] Get top 1 row of each group

25 Answered Questions

5 Answered Questions

[SOLVED] Stored procedure - return identity as output parameter or scalar

2 Answered Questions

[SOLVED] Appropriate Uses of the `IDENTITY` in TSQL

Sponsored Content