Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Introduction

In most of  our application scenario we need to get latest inserted row information through SQL query. And for that we have multiple options like

@@IDENTITY,
SCOPE_IDENTITY,
IDENT_CURRENT
All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

Compare  

@@IDENTITY :

It returns the last identity value generated for any table in the current session, across all scopes.

Let I explain this, suppose we create an insert trigger on table which insert a row in another table with generate an identity column then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY :

It returns the last identity value generated for any table in the current session and the current scope.

Let I explain this, suppose we create an insert trigger on table which insert a row in another table with generate an identity column then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is effected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT :

It returns the last identity value generated for a specific table in any session and any scope.

In other words we can say it is not affected by scope and session, it is only depend on a particular table and return that table related identity value which is generated in any session or scope.

SQL Query  

I am explaining the above process with help of some sample query, hope it helps-

1. CREATE

 TABLE Parent(id int IDENTITY);  

CREATE TABLE Child(id int IDENTITY(100,1));  

GO  

  

CREATE TRIGGER Parentins ON Parent FOR INSERT  

AS  

  

BEGIN  

   INSERT Child DEFAULT VALUES  

END;  

GO  

  

--End of trigger definition  

  

SELECT id FROM Parent;  

--id is empty.  

  

SELECT id FROM Child;  

--ID is empty.  

   

  

--Do the following in Session 1  

INSERT Parent DEFAULT VALUES;  

SELECT

Posted via email from .NET Info