Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1380
Home > Articles > Sql Server >
Post

Difference Between SCOPE_IDENTITY() and @@IDENTITY

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

Post Article | Search Articles | Articles Home
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.
Bookmark It

Login to vote for this post.

Latest Articles

More Articles ...

Latest Articles from Lakhangarg

More articles from Lakhangarg

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)