SQL-Server common problem - Retrieve ID just inserted

Question

I've looked at some other questions, they seem to be unrelated to SQL-Server.

Given the simple example query:

INSERT INTO tblComments (comment, user) VALUES ('lol', 'Guest')

Now suppose after the new user is created I need to insert some more data in other tables that require the last entered ID, I would do:

SELECT TOP 1 ID FROM tblComments ORDER BY ID DESC

To retreive the last entered ID, (hoping) that no queries have executed in the meantime, and that the returned ID is indeed for the value of the previous query.

I do keep my tables normalised, however I sometimes run into the situation where the inserted value may not necesserially be unique, so how can I guarantee the returned ID is the last one inserted?

Answer 1

Your approach is subject to a race condition.

Instead, use SCOPE_IDENTITY:

select scope_identity()

Answer 2

In addition to @RedFilter's suggestion you should also look at:

1. @@IDENTITY if you want the last identity that was inserted into any table in the current session even if the insert wasn't done in the current scope.
2. IDENT_CURRENT to get the last identity inserted in a specific table regardless of session or scope.

In your case SCOPE_IDENTITY is probably the way to go unless you want to get that identity even if the it is inserted in a different session or scope.

And just to be clear SCOPE_IDENTITY returns the last identity inserted into any table in the current session and scope.