Solved

Difference between @@identity and scope_identity()

Posted on 2013-01-22
5
931 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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