Difference between scope_identity(), @@Identity & ident_current()

First of all, let me explain two very basic concepts - what is Session and what is Scope:

Session: the current connection that's executing the command.
Scope: immediate context of a command.

For example:

1) Two commands executed within same stored procedures are in same Scope
2) Two stored procedure executing using same connection are in same Session.

As these two concepts are defined, here are the differences between the three identity retrieval methods:

* @@identity returns the last identity value generated in this session but any scope
* scope_identity() returns the last identity value generated in this session and this scope
* ident_current() returns the last identity value generated for a particular table in any session and any scope

Note: scope_identity() & iden_current() is applicable to SQL Server 2005 only