35

Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?

  1. SCOPE_IDENTITY()
  2. Aggregate function MAX()
  3. SELECT TOP 1 IdentityColumn FROM TableName ORDER BY IdentityColumn DESC
  • 1
    Use postgreSQL and you will have it from the shelf postgresql.org/docs/9.1/static/sql-insert.html – Yevgeniy Afanasyev Feb 14 '18 at 23:59
  • Leftfield option - if you have a Guid column in the table and you can generate a new Guid and insert it into the new column during the insert - you can then select the row with that Guid to pull out the generated int identity. – niico Nov 12 '18 at 12:52
56

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated.

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

Result:

----
1

Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated.

INSERT #a(x) 
  OUTPUT inserted.identity_column 
  VALUES('b'),('c');

Result:

----
2
3

and why this is best faster option?

Performance aside, these are the only ones that are guaranteed to be correct in the default isolation level and/or with multiple users. Even if you ignore the correctness aspect, SQL Server holds the inserted value in SCOPE_IDENTITY() in memory, so naturally this will be faster than going and running your own isolated query against the table or against system tables.

Ignoring the correctness aspect is like telling the mailman he did a good job delivering today's mail - he finished his route 10 minutes faster than his average time, the problem is, none of the mail was delivered to the right house.

Do not use any of the following:

  • @@IDENTITY - since this can't be used in all scenarios, for example when a table with an identity column has a trigger that also inserts into another table with its own identity column - you will get the wrong value back.
  • IDENT_CURRENT() - I go into detail about this here, and the comments are useful reading as well, but essentially, under concurrency, you will often get the wrong answer.
  • MAX() or TOP 1 - you would have to protect the two statements with serializable isolation in order to ensure that the MAX() you get isn't someone else's. This is much more expensive than just using SCOPE_IDENTITY().

These functions also fail whenever you insert two or more rows, and need all the identity values generated - your only option there is the OUTPUT clause.

  • One more question triggered in my memory. When ever we need to get last identity generated in a specific table either by any session or user the only correct and best way is MAX() of that column? – AA.SC Dec 31 '15 at 5:56
  • how about when i insert multiple rows in a table will SCOPE_IDENTITY() will always return the last generated identity? What if column is primary Key but not Identity column ? – AA.SC Dec 31 '15 at 7:12
  • @AA.SC yes, it will return the last one. If it's not an identity column, no, none of these functions will work. Where does the PK value come from in that case? – Aaron Bertrand Dec 31 '15 at 11:55
  • I have seen this for a column in our application where column is of INT type, and developers use MAX(columnname)+1 when ever they need to insert a new record – AA.SC Dec 31 '15 at 12:01
  • Then they already know what value they just inserted. SQL Server doesn't have any way to tell you that (you can't rely on pulling MAX again after the fact, unless you've completely isolated your entire transaction, which will not be good for performance or concurrency). – Aaron Bertrand Dec 31 '15 at 12:04
7

Apart from performance, they all have rather different meanings.

SCOPE_IDENTITY() will give you the last identity value inserted into any table directly within the current scope (scope = batch, stored procedure, etc. but not within, say, a trigger that was fired by the current scope).

IDENT_CURRENT() will give you the last identity value inserted into a specific table from any scope, by any user.

@@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope. (Side note: Access uses this function, and thus has some issues with triggers that insert values into tables with identity columns.)

Using MAX() or TOP 1 can give you entirely wrong results if the table has a negative identity step, or has had rows inserted with SET IDENTITY_INSERT in play. Here's a script demonstrating all of these:

CREATE TABLE ReverseIdent (
    id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
    data char(4)
)

INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')

SELECT * FROM ReverseIdent

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000

SET IDENTITY_INSERT ReverseIdent ON

INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')

SET IDENTITY_INSERT ReverseIdent OFF

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005

Summary: stick with SCOPE_IDENTITY(), IDENT_CURRENT(), or @@IDENTITY, and make sure you're using the one that returns what you actually need.

  • 1
    Why do you encourage the use of IDENT_CURRENT() and @@IDENTITY when your own script demonstrates that they output incorrect results? – Aaron Bertrand Dec 30 '15 at 14:51
  • 1
    @AaronBertrand I'm not sure I follow. The last identity value generated was 8998 (notice the step is -1), and that's what IDENT_CURRENT() returns. MAX() never returns the right value beyond the first row, since id is counting backwards, and with IDENTITY_INSERT on, 9005 is not a generated identity value, thus not reflected by IDENT_CURRENT(). But it may return "incorrect" results if you're really after what SCOPE_IDENTITY() returns. Choose the right tool for the job. – db2 Dec 30 '15 at 16:37
  • The OP seems to be after the identity value they inserted - in this case 8998 is incorrect. The edge cases you mention (backwards increment and IDENTITY_INSERT on) even further argue against using IDENT_CURRENT in my opinion, and @@IDENTITY shouldn't ever really be used because of the danger of triggers (now or added later). I'm still struggling to understand why IDENT_CURRENT would be the one the OP would want to use (especially under concurrency) or why @@IDENTITY would ever be used by anyone when much more reliable methods exist. – Aaron Bertrand Dec 30 '15 at 16:42
  • @AaronBertrand It's not 100% clear from the question that the desired result is the last insert from the current scope (option 1 differs from 2 and 3 in that regard), so I figured it would be a good idea to describe both and how they differ. But I do agree that @@IDENTITY is almost never the ideal way to get generated identity values. The main point is that MAX() or TOP 1 are like a less reliable version of IDENT_CURRENT(), which is a perfectly fine function to use if you understand what it does. Could be useful for maintenance jobs or something. – db2 Dec 30 '15 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.