If you are using
select SCOPE_IDENTITY();
or
select @@IDENTITY;
to determine the last sequence number added to an identity column you must keep in mind that these are session and scope depended functions.
- SCOPE_IDENTITY() returns the last inserted identity value within the scope of the current session.
- @@IDENTITY returns the last inserted identity value within the current session.
Since both function are table independent you run the risks of getting the identity value for the wrong table. It’s better to use IDENT_CURRENT(‘TableName’);
Till Next Time
Also useful to note is that IDENT_CURRENT("TableName"); is session and scope independent as well - meaning that it will retrieve the last identity added by any session or scope. This is useful when you need to retrieve the current identity value of a table before you are performing any INSERT operations. In most cases, when we are inserting into a table and want to retrieve the identity value of that inserted record, our best bet is using SCOPE_IDENTITY(), since it will fetch the last identity added through our current session and scope.
ReplyDelete