use of @@identity,scope_identity() and IDENT_CURRENT('') in sql server | Example Difference of @@identity,scope_identity() and IDENT_CURRENT('')

Difference between @@identity,scope_identity() and IDENT_CURRENT('')

1. @@identity :

select @@identity will return the last identity value generated for any table.

Example :
Suppose we have a table and we are inserting data into that table. And a trigger gets executed when any insert operation is done for that table. Then Select @@identity will return the last identity value generated for the table inside the trigger.

2. scope_identity()

select scope_identity() will return the last identity value generated for a table which is executed in the same scope i.e stored procedure, function, insert query.

Example :
Suppose we have a table and we are inserting data into that table. And a trigger gets executed when any insert operation is done for that table. Select scope_identity() will return the last identity value generated for the table in which the data is being inserted and not for the table inside the trigger.

3. IDENT_CURRENT('')

return the last identity value generated for the specific table .

Example:

1. Select IDENT_CURRENT('Employee')
2. Select IDENT_CURRENT('Department')