Can @@IDENTITY give inaccurate answers?
Yes it can.ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.
In almost every case, you should use scope_identity() instead. @@Identity returns the most recently created identity for your current connection. When you first use it, it might work fine - until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Likely not the one you were looking for. According to MSDN site, scope_identity():
“Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.”
Back
Sponsored links
Build IT Knowledge with Current & Trusted Content
Helps Employees Develop & Hone New Technical Programming Skills. Sign Up & Get Full Access.
Helps Employees Develop & Hone New Technical Programming Skills. Sign Up & Get Full Access.
Check Out IT Certification Preparation Materials
Sign Up With SkillSoft & Get Access to Training Materials for Over 50 Professional Certifications.
Sign Up With SkillSoft & Get Access to Training Materials for Over 50 Professional Certifications.
Villanova University Six Sigma & IT Certificate Programs
100% Online programs in Six Sigma, IS Security, CISSP Prep, Business Analysis, Proj. Mgmt. and more!
100% Online programs in Six Sigma, IS Security, CISSP Prep, Business Analysis, Proj. Mgmt. and more!
Key Elements to an Effective Business Continuity Plan
Learn to develop a plan that clarifies what is critical and sets specific recovery requirements.
Learn to develop a plan that clarifies what is critical and sets specific recovery requirements.
Web based bug tracking - AdminiTrack.com
AdminiTrack offers an effective web-based bug tracking system designed for professional software development teams.
AdminiTrack offers an effective web-based bug tracking system designed for professional software development teams.
