SQL Server 2000 has three similar functions: scope_identity, ident_current, and @ identity. They both return values inserted into the identity column.
1) The ident_current method returns the last generated id value for any session and a specific table in any scope. It is not restricted by the scope and session, but by the specified table.
2) @ identity variable returns the last generated id value for any table in all scopes of the current session.
3) The scope_identity method returns the last generated id value for the current session and any table in the current scope.
Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.
For example, there are two tables T1 and T2, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.
Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1. @ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.