Get the id of the newly inserted row

There are times when we need to retrieve the last inserted identity of a row. Here is a scenario

1. You need to insert a record into a table
2. You need the Id of the row you inserted, so you can use it to insert rows into tables referencing the first row

SQL Server has the options:

SCOPE_IDENTITY()– Gives the last identity value that was generated in the current scope. In other words, it will give the last identity value that you have explicitly created.

@@Identity –Gives the last identity value that was generated in the current session but in any scope.

IDENT_CURRENT(’tablename’) – Gives the last identity value produced in the table regardless if the connection it the scope.

In our scenario, SCOPE_IDENTITY() works perfect to return the identity of the recently added row.