Is IDENT_CURRENT the next @@identity

I've just finished watching the webcast on building a wareshouse according to Kimball principles. In it they generate an audit record by inserting a record and then using IDENT_CURRENT to get the identity value.

A few months ago some one on the forums was screaming because the IDENT_CURRENT was not working on a 64 bit server it was returning NULL.

My point is they both wanted the last identity generated for a table, but both missed the point that IDENT_CURRENT is not specific to a session but is server wide. This means you could be getting the identity value generated for an insert in another session. Pretty much like calling select MAX(identityCol) from table (but without the locking).

So just as people with triggers got burnt with the use of @@identity so are people with IDENT_CURRENT() whats more it probably won't show up as a real problem i.e. a failure, you will just have records associated with the wrong parent, your database will slowly become corrupt.

In both of these situations the user should be using SCOPE_IDENTITY to get the value generated by their last insert statement.


-
Published Tuesday, July 11, 2006 8:15 PM by simonsabin
Filed under: , ,

Comments

Monday, September 11, 2006 8:34 PM by Louis Davidson

# re: Is IDENT_CURRENT the next @@identity

Of course, it would be nice if they could get a function that works if you are using an instead of trigger to do the insert, that would be cool!