Identity Property Part – 3

This is the third part of this blog series. You can read the 2^nd part here Here. In this blog post we’ll learn how to retrieve last generated identity value. There are scenarios where we need to fetch the last inserted identity value, for example in a transaction we may first generate OrderID for order header table and then enter item details for that order in OrderItem table.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY all of them are used to retrieve last inserted identity value but all of them return value depending on scope and session. Let us see how can we fetch last inserted identity value in SQL Server.

SCOPE_IDENTITY:

It returns the last Identity value inserted within the current scope for any table. A scope is a module that can be a stored procedure or trigger or function or batch. It returns identity value. This function will return NULL if it is invoked before any insert statements into an identity column occurs in the scope.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)
GO
INSERT Employee (EMPNAME ) VALUES (‘Sandip’),(‘Amit’)
GO
SELECT EMPID , EMPNAME FROM Employee
SELECT SCOPE_IDENTITY() IDENTITY_VALUE

Example 2:

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)
GO
—Create an INSERT trigger on Employee
IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL
DROP TRIGGER TRG_Employee
GO
CREATE TRIGGER TRG_Employee
ON Employee
AFTER INSERT
AS
INSERT Employee (EMPNAME ) VALUES (‘Sunil’)
SELECT SCOPE_IDENTITY() WithinTrigger
GO
–Insert in current scope
INSERT Employee (EMPNAME ) VALUES (‘Sandip’)
GO
SELECT SCOPE_IDENTITY() InCurrentScope
SELECT EMPID, EMPNAME FROM Employee

IDENT_CURRENT

It returns the last IDENTITY value produced in a table, irrespective of the connection and the scope of the statement. It is not dependent on any scope and session. This function takes table name as parameter.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)
GO
—Create an INSERT trigger on Employee
IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL
DROP TRIGGER TRG_Employee
GO
CREATE TRIGGER TRG_Employee
ON Employee
AFTER INSERT
AS
INSERT Employee (EMPNAME ) VALUES (‘Sunil’)
SELECT IDENT_CURRENT() WithinTrigger
GO
–Insert in current scope
INSERT Employee (EMPNAME ) VALUES (‘Sandip’)
GO
SELECT IDENT_CURRENT() InCurrentScope
SELECT EMPID, EMPNAME FROM Employee

@@IDENTITY

This system function returns the last generated identity value for any table in the current session and is not limited to any specific scope.

If any insert statement on a table causes a trigger to fire on another table which cause to generate an identity value, you will get the last identity value that was created last.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)
GO
—Create an INSERT trigger on Employee
IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL
DROP TRIGGER TRG_Employee
GO
CREATE TRIGGER TRG_Employee
ON Employee
AFTER INSERT
AS
INSERT Employee (EMPNAME ) VALUES (‘Sunil’)
GO
INSERT Employee (EMPNAME ) VALUES (‘Sandip’)
GO
SELECT @@IDENTITY() LastIdentityvalue
SELECT EMPID, EMPNAME FROM Employee