Difference Between SCOPE_IDENTITY() and @@IDENTITY

Lakhangarg
Posted by in Sql Server category on for Beginner level | Views : 10939 red flag

Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table.
Introduction
Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table. but there is some difference between the two:

SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.

Example
Let us suppose we have two tables named table1 & table2... and we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.

in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.

Page copy protected against web site content infringement by Copyscape

About the Author

Lakhangarg
Full Name: Lakhan Pal
Member Level: Silver
Member Status: Member,Moderator
Member Since: 8/17/2009 12:39:46 AM
Country: India

http://lakhangarg.blogspot.com
Hello Friends Myself Lakhan Pal Garg and i am a B.Tech (IT) Graduate and having 8+Years of Exp. in Microsoft Technology. I have Write a Blog Named Free Code Snippets (http://lakhangarg.blogspot.com/) I hope you must visit my blog as your valuable feedback will motivate me to write more and improve my mistake. If you want to gain more knowledge then share it with others.

Login to vote for this post.

Comments or Responses

Posted by: Parasu on: 8/20/2009
Nice examples

-Paras.
Posted by: Khaleek_ahmad on: 3/1/2011 | Points: 25
@@Identity is a globa variable used for to get the last generated id globally. While scope_identity used for to get the last generated id in a scope(procedure or function).
For more detail see at :- <a href="http://interview-preparation-for-you.blogspot.com/2011/02/identity-and-scope-identity.html</a>
Posted by: Khaleek_ahmad on: 3/1/2011 | Points: 25
@@Identity is a globa variable used for to get the last generated id globally. While scope_identity used for to get the last generated id in a scope(procedure or function).
For more detail see at :- <a href="http://interview-preparation-for-you.blogspot.com/2011/02/identity-and-scope-identity.html">http://interview-preparation-for-you.blogspot.com/2011/02/identity-and-scope-identity.html</a>

Login to post response

Comment using Facebook(Author doesn't get notification)