Identity Property Part – 3

This is the third part of this blog series. You can read the 2nd part here HereIn 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

identity value using Scope Identity

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

identity value impact on trigger

   

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 value through IDENT_CURRENT

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

identity value through @@Identity

 

   

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.