Search This Blog

Difference between @@Identity,Scope_Identity() and Ident_Current in SQL

Difference between @@Identity,Scope_Identity() and Ident_Current()


Most of the developers are aware of this fact that above three @@Identity,Scope_Identity() and Ident_Current returns the Last value inserted.
.
So below i will explain the Difference between all 3 with Examples

--Creating sample table tbl1 with identity field

CREATE TABLE tbl(Empid int IDENTITY)

INSERT tbl1 DEFAULT VALUES

Case I-Single Table Insert without Trigger
--Run queries all will return 1 
SELECT @@IDENTITY

SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT('tbl' )

Case II-Table with Trigger 

Now we will add a trigger in tbl that will have an insert action in table 2

--creating table 2
CREATE TABLE tbl0(Empid int IDENTITY(10,5))--start with 10 with increament of 5

--creating trigger to check behaviour of all 3

create trigger T_ins on Tbl
for insert
as begin
insert tbl0 default values
end

INSERT tbl DEFAULT VALUES

--This returns  last value inserted across scope(second scope i.e trigger tbl0) which  is tbl0 values 10
SELECT @@IDENTITY

--This returns the value inserted into identity column in curent scope that is tbl value 2
SELECT SCOPE_IDENTITY()

-This returns the last identity value generated for a specific table in any session and any scope
SELECT IDENT_CURRENT('tbl' ) --2
SELECT IDENT_CURRENT('tbl0' )--10

No comments:

Post a Comment