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

I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY in Sql Server but don’t understand the pros and cons attached to each.

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

One thought on “What is the best way to get identity of inserted row?”

  1. There are 4 different system functions in SQL Server that return the identity of an inserted row.

    @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes in Sql Server. What you need to be careful about here, is that it’s across scopes. The value you get might not be from your current statement, but might come from other places like the execution of a trigger.

    SCOPE_IDENTITY is the one you generally what to use. It returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

    IDENT_CURRENT returns the last identity value generated for a specified table or view. The last identity value generated by IDENT_CURRENT can be for any session and any scope.

    The OUTPUT clause allows you to access every row that was inserted via that statement. Since the clause is scoped to the specific statement it easier to use than the other functions above. You would have to insert the results into a Sql Server table variable or a temp table to gain your information. One of the advantages of the output clause is that it gives results even in an error scenario where the statement is rolled back. One of the downsides to this function is that it is executed before triggers are executed and cannot return trigger-generated values.

Comments are closed.