CODEWITHSUNDEEP
sql-serveridentityunsafe
Maddy.Shik
Maddy.Shik

Reputation: 6787

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

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 sql server 2005:is it safe to use @@identity? for lock on identity issue

Upvotes: 3

Views: 726

Answers (2)

HLGEM
HLGEM

Reputation: 96552

@@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.

Upvotes: 3

BobbyShaftoe
BobbyShaftoe

Reputation: 28499

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.

Upvotes: 8

Related Questions