Difference between @@identity and scope_identity()

Posted on 2013-01-22
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?
Question by:Easwaran Paramasivam
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
LVL 25

Expert Comment

by:Luis Pérez
ID: 38804388
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.
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.
LVL 57

Accepted Solution

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.
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 38805269

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 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