Identity Insert Options

-August 2009+


  • Entries (3)
  • Comments (0)


Posted by Philip Leitch Friday, August 7, 2009 1:45:00 AM Categories: Transact SQL
Rate this Content 0 Votes
Here are the Help definitions:
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.
Normally I would use SCOPE_IDENTITY - because that will return the last ID the current script inserted. That is good because another session might have inserted a transaction just after, but at almost the exact same time. SCOPE_IDENTITY returns just my insert. If I use IDENT_CURRENT the other user's inserted row ID will be returned (which isn't what I want). In fact IDENT_CURRENT and (Select max(ROW_ID)) have caused headaches in our debtor transaction because of this.
If you don’t know which database you are inserting into until run time. Normally the best way around this is to generate a string executed statement. The problem is that the string is executed in a new scope. SCOPE_IDENTITY, therefore, doesn't return a thing - or returns the last insert from the scope - which won't be from the table we just inserted into (could even be the notes table or similar).
It turns out that @@IDENTITY is the function to use. It will return the last row ID inserted through the current session, but irrespective of scope. Since the executed string is in the same session (different scope), this will return the rowid of the inserted row, even if it is in another database.
I haven't used it in the past because if a trigger does something like insert a discount or something (which it doesn't do right now), it will return that row's ID rather than the row you inserted. I only ever want the ID that I inserted. I don't think that will be an issue for the moment....
Copyright 2009 Philip Leitch