[SQL Server] Retrieving last generated Identity value

@@IDENTITY:

1. Returns the last IDENTITY value generated implicitly (via trigger or user defined function) or explicitly (by user) in current session.
2. It returns the value regardless of the scope of the statement and the table.

SCOPE_IDENTITY():

1. Returns the last IDENTITY value generated explicitly in current session and by a statement in the same scope.
2. It returns the value regardless of the table, generated explicitly rather than any identity that was created by a trigger or a user defined function.

IDENT_CURRENT(<table-name>):

1. Returns the last IDENTITY value generated in a table.
2. It returns the value regardless of the session and the scope of the statement.