Last Identity Value In The Scope

The problem is finally resolved in the proper manner in SQL Server 2000. The new SCOPE_IDENTITY function returns the last identity value generated in the current scope of the current process. We will add this function to the code executed earlier against tempdb database:

Insert into b (b_desc) 
Values ('1') 

Insert into a (a_desc, b_desc) 
Values ('aaa', 'bbb') 

Select @@identity [@@Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()]

When you execute it, notice that the SCOPE_IDENTITY function returns the proper result:

(1 row(s) affected) 

(1 row(s) affected) 

@@Identity                               SCOPE_IDENTITY()             
---------------------------------------- ----------------------------- 
4                                         2 

(1 row(s) affected) 

Therefore, we now recommend that you use SCOPE_IDENTITY instead of @@identity.

Last Identity Value in the Table

There is one more identity-related function in SQL Server 2000. IDENT_CURRENT returns the last identity value set on a specified table (in any scope of any process). To use it, just supply the table name as a parameter:

Select IDENT_CURRENT('Equipment')

The value may be equal to values obtained using other methods (using other identity or MAX functions), but it may also be different.