sql server 2005:is it safe to use @@identity?

Question

i have a procedure in which i am inserting record in employee table.nad getting empid by using @@identity ? when this procedure will be called by more than one user at same time,there can be possibility that it returns identity of some other employee inserted at same time.because there is no lock on identity by system?

--code --identity on for empid column
insert into employee (name) values ('sahil'); return @@identity

refer http://stackoverflow.com/questions/882460/sql-server-2005is-it-safe-to-use-identity for lock on identity issue

Answer 1

You should be using SCOPE_IDENTITY() instead. However, @@IDENTITY refers to the current connection so other users won't affect you but there are other issues to consider.

More information here.

Answer 2

@@identity is not safe to use. If the table has a trigger with an insert to a differnt table with an identity that is the value that will be returned. Never use it to get the idnetity value you just inserted. You may think well I don't have a trigger now, but you never know when one might be added and you can go a long time before realizing that your data is hopelessly messed up.