Solved

Difference between @@identity and scope_identity()

Posted on 2013-01-22
5
905 Views
Last Modified: 2013-01-22
I know that  @@identity is scope less and scope_identity() will return inserted value within the scope. In my DB if I replace all @@identity with scope_identity()? Will it work or would have any impact?

If no issue with the replacement why do we need @@identity?
0
Comment
Question by:Easwaran Paramasivam
5 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
Comment Utility
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 83 total points
Comment Utility
They are very different scope_identity() is within the current scope whereas @@identity is not.

So for example if you have a stored procedure (SP1) that calls another stored procedure (SP2), each of which inserts a records, but SP2 inserts the second record.

From within SP1 scope_identity will return the identity of the insert in SP1 whereas @@identity in SP1 will return the identity inserted in SP2.

Same applies to triggers and any other nested commands.

So hope it explains how they are totally different things. And you can't replace then without investigating what behaviour you actually want in each situation.

http://msdn.microsoft.com/en-us/library/ms190315.aspx
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 83 total points
Comment Utility
The only time it may make a difference is if there is a trigger called after the insert in your stored procedure which inserts something @@identity will return the identity value from the insert done by the trigger rather than your stored procedure, whereas scope_identity() will always return the identity value for the insert done by the current scope i.e. our stored procedure.

Generally scope_identity() is safer to use as if a trigger is added to a table after the stored procedure is created you will still get the correct value in the stored procedure.

@@identity could be used if there was some reason that you wanted the inserted value of the trigger for use in the stored procedure.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 84 total points
Comment Utility
>> I know that  @@identity is scope less and scope_identity() will return inserted value within the scope. In my DB if I replace all @@identity with scope_identity()? Will it work or would have any impact?

No, it is wrong. Only IDENT_CURRENT is scopeless.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; Scope of @@IDENTITY function is current session on the local server on which it is executed.

>> If no issue with the replacement why do we need @@identity?
To identify any values generated within your session on other tables via triggers.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
Comment Utility
Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now