Solved

Difference between @@identity and scope_identity()

Posted on 2013-01-22
5
911 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
ID: 38804388
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 83 total points
ID: 38804391
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
ID: 38804402
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
ID: 38805036
>> 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
ID: 38805269
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

24 Experts available now in Live!

Get 1:1 Help Now