There are times when we need to retrieve the last inserted identity of a row. Here is a scenario
- You need to insert a record into a table
- 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.