Chittibabu

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
 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-

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 @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

View post: