Last generated identity value on table in SQL SERVER 2005

SQL SERVER provides three different functions for capturing the last generated identity value on a table that contains an identity column:-


(a) select  @@IDENTITY

(b) select  SCOPE_IDENTITY()

(C) select  IDENT_CURRENT('tableName')


IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.