Many Developer come across situation in which they fail figuring out difference in IDENT_CURRENT, @@IDENTITY, SCOPE_IDENTITY.

let’s understand What all 3 function returns.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
• Let’s Execute following Script to create table in which we will Insert identity  and see what all 3 function returns.

--Block A:Create Table
CREATE TABLE Employee
(  EMP_id INT IDENTITY
PRIMARY KEY ,
Emp_Name VARCHAR(100) ,
Emp_City VARCHAR(100) ,
EMP_Designation VARCHAR(100)
)
--We storing history of employee progress as employee enters in organization
CREATE TABLE employee_History
(
HistoryID INT IDENTITY(100, 1) ,
Emp_ID INT ,
EMP_Name VARCHAR(100) ,
Designation VARCHAR(100) ,
Actionstatus VARCHAR(10) ,--Insert/Update/Delete
dtDate DATE
)
Go
--Block2:Create Trigger Block
CREATE TRIGGER TRI_Insert_Employee ON Employee
FOR INSERT
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtdate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'Insert' ,
GETDATE()
FROM    INSERTED
GO
CREATE TRIGGER TRI_Update_Employee ON Employee
FOR UPDATE
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtDate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'UPDATE' ,
GETDATE()
FROM    INSERTED
GO
CREATE TRIGGER TRI_DELETE_Employee ON Employee
FOR DELETE
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtDate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'DELETE' ,
GETDATE()
FROM    DELETED
GO

Let’s differ all differ all 3 function in easy way.

SCOPE_IDENTITY() =last identity ,any table,current session ,current scope.

@@IDENTITY           =last identity ,any table ,current session ,all scope.

IDENT_CURRENT    =last identity ,specific table ,any session,any scope.

let’s assume that diffrent developer have used diffrent function to get inserted identity.

Block 1:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'Johansan Keyas' ,
'New York' ,
'SQL Developer'
)
SELECT @@IDENTITY
/*Output: 100  */
SELECT SCOPE_IDENTITY()
/*Output: 1    */
SELECT IDENT_CURRENT('Employee')
/*Output: 1    */

• When we executing Block 1 in this session current scope value 1  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 1.
• We have one insert trigger on employee table ,now it executes in this session but scope is  changed so @@IDENTITY returns value 100
• IDENT_CURRENT returns the perfact value.
• So in this Block 1 Scenario Developer who has not used @@IDENTITY  has winning situation.

Lets create trigger on employee table ,this scenario may never going possible,but let’s take  this example to understand when we may have a problem in getting right value inserted by developer.

Block  2:

CREATE TRIGGER TRI_Employee_Insert2 ON dbo.Employee
FOR INSERT
AS
INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES  ( 'Unknown',
'unkwnon',
'unknown'
)
GO

Lets Execute Block 3

Block 3:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'John Wills' ,
'New York' ,
'SQL Developer'
)
SELECT @@IDENTITY/*Output: 102*/
SELECT SCOPE_IDENTITY()/*Output: 2*/
SELECT IDENT_CURRENT('Employee')/*Output: 3*/

• When we executing Block 3 in this session current scope value 2  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 2.
• We have two insert trigger on employee table ,now it executes in this session but scope is changed so @@IDENTITY returns value 102 which is inserted at last in current session.
• IDENT_CURRENT returns value 3 which is inserted by trigger which is created in Block 3 Session.

Lets Disable trigger that insert value in employee_History on insert on Employee table

Block 4:

DISABLE TRIGGER TRI_Insert_Employee ON Employee
GO

Block 5:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'Krishnan Narayan' ,
'Banglore' ,
'SQL Developer'
)
SELECT @@IDENTITY/*5*/
SELECT SCOPE_IDENTITY()/*4*/
SELECT IDENT_CURRENT('Employee')/*5*/

• When we executing Block 5 in this session current scope value 4  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 4.
• We have one insert trigger which has self insert on employee table ,now it executes in this session but scope is changed so @@IDENTITY returns value 5.
• IDENT_CURRENT returns value 5 which is inserted by trigger which is created in Block 5 Session.

let’s take situation to see what happen when insert fails ,what this function retunrs the value.

we will provide long string so let’s reduce size of column.

Execute block 6

Block 6:

ALTER TABLE dbo.Employee
ALTER COLUMN Emp_City VARCHAR(10)
Go
DISABLE TRIGGER TRI_Employee_Insert2 ON Employee
Go

Before Running Following Block Please seee

Last identity inserted in Employee table it is 5 in my case.

Now Execute Block 7

Block 7:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES  ( 'Krishnan Narayan' ,
'Visakhapatnam' ,
'SQL Developer'
)
SELECT  @@IDENTITY/*5*/
SELECT  SCOPE_IDENTITY()/*4*/
SELECT  IDENT_CURRENT('Employee')/*6*/

We get following error after executing Block 7

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

• Insertion is failed but we getting value in all 3 Identity Function and all 3 are different.

So Developer must while they are working with this function before using it into code.