All SQL Server functions that retrieve the last inserted value into the Identity column of a table - @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.
IDENT_CURRENT is the best way.
@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope (A scope could be one of these - stored procedure, trigger, function, or batch) @@IDENTITY is not limited to a specific scope.
For e.g. Let's say , there are two tables 'tablea' and 'tableb', both having identity columns. Lets define an INSERT trigger on 'tablea', that inserts a row on 'tableb' . Run an INSERT statement on 'tablea'
After a row is inserted into 'tablea' and 'tableb', @@IDENTITY and SCOPE_IDENTITY() will return different values. @@IDENTITY will return the 'table2'-Identity column value. SCOPE_IDENTITY() will return the 'table1'-Identity column value, and IDENT_CURRENT(tablename) will return the corresponding last inserted identity column value.
Use SCOPE_IDENTITY() , if you are not sure, if some triggers are going to be written or not on a table. Or, even better, use IDENT_CURRENT(tablename) to be more specific. |