• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 949
  • Last Modified:

Difference between @@identity and scope_identity()

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
3 Solutions
 
Luis PérezSoftware Architect in .NetCommented:
0
 
Dale BurrellCommented:
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
 
Paul JacksonCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now