What are @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(‘table_name’) in SQl Server?


@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(‘tablename’) are used to get last inserted identity of record.

  • @@IDENTITY : It is used to get last inserted identity of record. It is limited to current Session but it is not limited to current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
  • SCOPE_IDENTITY() : It is used to get last inserted identity of record. It is limited to your current Session and also limited to your current scope. it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
  • IDENT_CURRENT(‘table_name’) : It is used to get last inserted identity of record. It is not limited to your current Session and current scope. It is limited to your table. It returns the identity value generated for a specific table in any session and any scope.