In SQL Server 2000, there are three similar features: they are: scope_identity, identity, Ident_current and @@ identity, they all returns the value inserted into the Identity column.
Ident_current returns the identifier value of the last generated to any session and any scope. Ident_current is not limited by the limitations and sessions, and is limited to the specified table. Ident_current returns the value generated by a particular table in any session and scope. @@ iDENTITY Returns the final generated identity value of any table in all scopes of the current session. Scope_identity Returns the identifier value generated by any table in the current session and current scope
Scope_identity and @@ identity returns the last identifier value generated in any table in the current session. However, scope_identity only returns a value inserted into the current scope; @@ identity is not limited to a specific scope.
For example, there are two tables T1 and T2 that define an INSERT trigger on T1. When a row is inserted into T1, the trigger is excited and inserted in T2. This example illustrates two scopes: one is inserted on T1 and the other is insertion of the result of the trigger in T2.
Suppose T1 and T2 have an Identity column, @@ identity and scope_identity will return different values for the last return of the INSERT statement on T1.
@@ identity returns the last Identity column value inserted into any scope in the current session, which is the value inserted in T2.
Scope_identity () returns the Identity value inserted in T1, which is the last INSERT that occurs in the same scope. If you call the Scope_identity () function before the insertion statement is inserted to the identity column, the function will return a null value.
The values returned by Ident_current ('T1') and Ident_Current ('T2') are the last self-increasing values of the two tables, respectively.
