@@Identity

Many TSQL 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.

@@Identity is potentially a very, very bad thing!  In almost every case, you should use scope_identity() instead.

Why?  @@Identity returns the most recently created identity for your current connection.  When you first use it, it might be 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?  Not nice.

scope_identity() is much nicer.  It gives you what you're expecting.

 

Print | posted on Wednesday, October 29, 2003 2:49 PM

Feedback

# This is Why I Love SQL Blogs

left by Sirsha Development Resources Blo at 10/29/2003 3:13 PM Gravatar

# re: @@Identity

left by Pretsel at 10/29/2003 5:39 PM Gravatar
Well shit me. Thanks for the random piece of info :D

Thats a really useful thing to know!

# re: @@Identity

left by Darrell at 10/29/2003 10:26 PM Gravatar
Yeah, we discovered this problem in an app a while back (2002 or so). As tech lead, I just went ahead and had everyone change all the procs to use scope_identity() instead of @@Identity, just in case. Especially since it took forever to track down the bug...

# Try not to use @@identity

left by James Geurts' Blog at 11/1/2003 5:54 PM Gravatar

# re: @@Identity

left by Jim Cheseborough at 11/1/2003 10:47 PM Gravatar
Thanks a million!

# re: @@Identity

left by Magnus at 11/2/2003 4:47 AM Gravatar
Note that scope_identity() only exists in SQL Server 2000.

# Why not to use @@Identity ??

left by Vinod Kumar's Blog at 11/17/2003 9:17 AM Gravatar

# re: Assignment with SQLHelper.ExecuteScalar() using a StoredProcedure in a single line. No can do.

left by Dave Burke at 4/2/2004 1:12 AM Gravatar

# Reminder - @@IDENTITY

left by JonGalloway.ToString() at 7/16/2004 6:51 PM Gravatar

# re: @@Identity

left by Pravangsu Biswas at 8/2/2006 4:44 PM Gravatar
Nice posting, It is informative and excellent.

# re: @@Identity

left by Carlos Gomez at 8/10/2006 7:53 AM Gravatar
So no scope_identity() on sql2005??

# re: @@Identity

left by Peter Wooster at 8/11/2006 3:12 PM Gravatar
scope_identity() works fine in sql2005.

# pre test

left by search.subscribe.share at 8/11/2006 7:47 PM Gravatar






ASP.NET Developer Center: Data Access: Tutorial 1: Creating a Data Access Layer
...

# pre test

left by search.subscribe.share at 8/11/2006 7:49 PM Gravatar





ASP.NET Developer Center: Data Access: Tutorial 1: Creating a Data Access Layer
...

# re: @@Identity

left by Ayaz Ahmad at 8/29/2006 8:58 PM Gravatar
useful info regarding @@Identity. i have been using this for quite a long time. since i was not using triggers thats y no such problem occured at that time. but still thanks ;) for updating me for future developments.

# re: @@Identity

left by aloks at 11/3/2006 6:31 AM Gravatar
I want to use identity and create alphanumeric gerenrate ID e.g. By calling through Store Procedure in ASp.NEt and VB.Net using Sql Express
AA-A101
AA-A102
AA-A103
AA-A104
....
....
...
AA-A999
AA-B101
AA-B102
AA-B103
AA-B104
....
....
...
AB-A999
AB-A101
AB-A102
AB-A103
AB-A104
....
....
....

ZZ-Z999

Comments have been closed on this topic.