SQLcommitted

Committed with SQL

SQL Server – Identity Property Part – 3

This is third part of this blog series. You can read the 2nd part here (SQL Server: Identity Property Part – 2).

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

image

 

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

image

 

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

image

 

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

image

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

December 7, 2013 - Posted by | Identity, Transact-SQL | , , , , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: