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.