Comparison of scope_identity, ident_current, and @ identity

The scope_identity, ident_current, and @ identity functions of SQL Server 2000 are similar. All three functions return the last identity value. However, in terms of scope and current session, each function has different definitions for the last identity value:

Ident_current returns the last generated identity value for any session and specific tables in any scope.

@ Identity returns the last generated identity value of any table in all scopes of the current session.

Scope_identity returns the last generated identity value for the current session and any table in the current scope.
 

Example: 
Create Table T6 (ID int identity );
Create Table T7 (ID int identity (100,1 ));
Go

Create trigger t6ins on T6 for insert
As
Begin
Insert T7 default values
End;
Go

Trigger definition ends

Select * From T6;

ID is empty
 
Select * From T7;

ID is empty

The following is Session 1.
Insert T6 default values;

Select @ identity;

The returned identity value is 100, which is generated by the insert trigger (the current session is T6, And the scope is T6 and T7)

Select scope_identity ();

The returned identity value is 1, which is generated by the insert statement (the current session is T6)

Select ident_current ('t7 ');

The returned identity value is 100, and the specified table is T7 (all sessions are T6, T7, and all scopes are T6 and T7)

Select ident_current ('t6 ');

The returned identity value is 1, and the specified table is T6 (all sessions are T6, T7, and all scopes are T6 and T7)

The following is Session 2.

Select @ identity;

The returned identity value is null because there is no INSERT command activity.

Select scope_identity ();

The returned identity value is null because there is no INSERT command activity.

Select ident_current ('t7 ');

Returns the last T7 identity value of 100.