How to get last inserted id in sql?

5.9K Asked by dipesh_9001 in SQL Server , Asked on Mar 17, 2023

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?

SCOPE_IDENTITY()
Aggregate function MAX()
SELECT TOP 1 IdentityColumn FROM TableName ORDER BY IdentityColumn DESC

Answered by David EDWARDS

The answer to your question - how to get last inserted id in sql is -

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 is this the fastest 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.

Your Answer

Answer (1)

Ranjana

To get the last inserted ID in SQL, you typically use the SCOPE_IDENTITY() function in SQL Server. This function returns the last identity value inserted into an identity column in the same scope (session, batch, or stored procedure).

Here's an example of how you can use SCOPE_IDENTITY():

-- Insert a row into a table with an identity column

INSERT INTO YourTable (Column1, Column2)

VALUES ('Value1', 'Value2');-- Get the last inserted ID

SELECT SCOPE_IDENTITY() AS LastInsertedId;

In this example:

Replace YourTable with the name of your table.
Replace Column1 and Column2 with the columns you're inserting values into.
'Value1' and 'Value2' are example values you're inserting into the table.

After inserting a row into the table, you can use SCOPE_IDENTITY() in the same scope to retrieve the last inserted ID.

It's important to note that SCOPE_IDENTITY() returns the last identity value inserted by the current session, and it's not affected by triggers or concurrent sessions inserting into the same table. If you're working with SQL Server, it's the most commonly used function for this purpose. However, other database systems like MySQL or PostgreSQL may have different methods to achieve the same result.

7 Months