RSS

@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

25 Oct

Sometimes we create an auto increment field that called (Identity Field) … But if we assume that we want to get the last ID that is included in the latest inserted row, for doing this we have some of options:-

  • @@IDENTITY
  • SCOPE_IDENTITY()
  • IDENT_CURRENT()

All of the previous options will return the last identity (ID) included in the last/latest inserted row, but each of them has some differences as a following:-

@@IDENTITY

It returns the latest identity value generated for the current table … Let’s show it in another way, suppose we try to insert new row using INSERT INTO T-SQL command in Students table which created below. This table has an identity field named ID … We will execute this T-SQL command:-

— (1). Create the table:

CREATE TABLE [dbo].[Students](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]

——————————————

— (2). Insert Data:

INSERT INTO [dbo].[Students] VALUES (‘Shady Kamel’,30);
INSERT INTO [dbo].[Students] VALUES (‘Omar Muhammad’,25);
INSERT INTO [dbo].[Students] VALUES (‘Magdy Salem’,22);

——————————————

— (3). Get the last ID’s value that inserted in this table:

SELECT @@IDENTITY

——————————————

(4). Result will be (Three: 3) as a latest value generated

But in this case we can not insert any value in the identity field manually and in the same time we can not know the identity value generated in the latest row that we inserted by the previous command. But we can use this clause to do this … SELECT @@IDENTITY.

In brief@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY()

Let’s assume that we have a parent table and another as child … we will create a trigger on the parent table in INSERT event that causes another INSERTĀ  in the child table … follow the next sql script to understand this case study:

— (1). Create tables

CREATE TABLE [Parent_Table] (MasterID INT IDENTITY);

CREATE TABLE [Child_Table] (ChildID INT IDENTITY(50,1));

Go

——————————————–

— (2). Create triggers

CREATE TRIGGER trgParent
ON [Parent_Table]
FOR INSERT
AS
BEGIN
INSERT [Child_Table] DEFAULT VALUES
END;

The previous code means that we have two tables, the first one named as (Parent_Table) and the second named (Child_Table). And also we have a trigger on the table named (Parent_Table), this trigger fired when inserting new row in the (Parent_Table) and causes a new insert action (INSERT T-SQL) into the (Child_Table) as shown above.

If you want to test this case run the following T-SQL commands:

INSERT [Parent_Table] DEFAULT VALUES;

SELECT @@IDENTITY;
— Returns the value 50. This was inserted by the trigger.

SELECT SCOPE_IDENTITY();
— Returns the value 1. This was inserted by the INSERT statement two statements before this query.

In briefSCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope.

@@IDENTITY vs. SCOPE_IDENTITY() in the last example: @@IDENTITY returns that identity record which is created by trigger, but SCOPE_IDENTITY() result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY() returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT()

We are still working on the previous example (Parent & Child tables) … You can run the following T-SQL command to observe the differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() …:

INSERT [Parent_Table] DEFAULTVALUES;

SELECT@@IDENTITY;
— Returns the value 50. This was inserted by the trigger.

SELECT SCOPE_IDENTITY();
— Returns the value 1. This was inserted by the INSERT statement two statements before this query.

SELECT IDENT_CURRENT(‘Child_Table’);
— Returns value inserted into Child_Table, that is in the trigger.

SELECT IDENT_CURRENT(‘Parent_Table’);
— Returns value inserted into Parent_Table. This was the INSERT statement four statements before this query.

In briefIDENT_CURRENT() returns the last identity value generated for a specific table in any session and any scope. In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

 
Leave a comment

Posted by on October 25, 2011 in MS SQL Server

 

Tags: , , , , , , , ,

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

 
%d bloggers like this: