top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

What is Scope identity and When is it used?

+2 votes
79 views
What is Scope identity and When is it used?
posted Aug 8, 2014 by Muskan

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes
 
Best answer

The SCOPE_IDENTITY() function returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

Lets first understand @@IDENTITY before we move on.
@@IDENTITY: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to an INSERT, and the trigger executes another INSERT statement).

Suppose you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Credit: http://technet.microsoft.com/en-us/library/aa259185(v=sql.80).aspx

answer Aug 8, 2014 by anonymous
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...