If you are directly reading this blog on getting last generated Identity column value my honest suggestion would be to go back and start from the beginning. In this blog, we will learn how to retrieve the last generated Identity column value in SQL server. In order to explain the problem, I will take the same old table Order which I have used in the previous blog on SQL. So we have an Order table with Identity set to true.

So I have inserted one record into the table and Id value will be generated by the SQL server as shown below:

INSERT INTO [ORDER] VALUES(‘123456789′,’3267’,1000,100,GETDATE())

So now in order to get the generated Identity value, we are going to SCOPE_IDENTITY() function as shown below:

Figure 1:0– Demonstrating SCOPE_IDENTITY() function in SQL server

There is another global function to get the last generated Identity column value:

@@IDENTITY

Now I have two records in our Order table:

INSERT INTO [ORDER] VALUES(‘123456789’,‘3267’,30000,2200,GETDATE())

So now we can also use @@IDENTITY to get the last generated Identity column value

SELECT @@IDENTITY

Figure 2:0– Demonstrating @@IDENTITY() function in SQL server

We can see both these function gives the same output but what’s the difference between them and where we should use them? In order to explain the same let’s take an example Here we are creating another table OrderNew with same Structure and now while inserting into the Order table we will create a trigger to insert the records into OrderNew when a record is inserted in Order table. Triggers are basically set of events you want to perform when certain Insert, delete, Update operation is performed on the table. We will be learning the same in the upcoming article.

GO
/****** Object: Table [dbo].[OrderNew] Script Date: 15-Apr-17 2:28:27 PM ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
TABLE [dbo].[OrderNew](
[ID] [int] IDENTITY(1,1)NOT NULL,
[CUSTOMERID] [nvarchar](100) NULL,
[STOREID] [nvarchar](10) NULL,
[GROSSTOTAL] [decimal](18, 0) NULL,
[TOTALORDERDISCOUNT] [decimal](18, 0)NULL,
[ORDERCREATEDDATE] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALOW_PAGE_LOCKS=ON)
ON [PRIMARY]
)ON [PRIMARY]
GO

Creating a Trigger:

CREATE TRIGGER TR_INSERT ON [ORDER] FOR INSERT
AS
BEGIN
INSERT INTO [OrderNew] VALUES(‘129’,‘3267’,30000,2200,GETDATE())
END

So now once we insert a record to Order table new records will be inserted to OrderNew table. We can then run both @@IDENTITY and SCOPE_IDENTITY to check the difference between each other as shown below:

Figure 3:0– Demonstrating difference betweeb SCOPE_IDENTITY and @@IDENTITY.

So we can see both the functions are resulting in two different outputs i.e. SCOPE_IDENTITY returning 4 and @@IDENTITY returning 1. Both of them return the last generated identity value.

Let’s discuss the SCOPE_IDENTITY(), as the name suggest it return the last generated Identity value for the current scope and session Scope here means insertion of records to table Order if we insert a record into another table it will give the result of last executed scope of same session. While @@IDENTITY() returns the last generated value across any scope i.e. the last Identity value generated was of Trigger so it returned the same.

We have another function IDENT_CURRENT(‘TABLENAME’) to return the last generated Identity value across any session(any user connected to SQL server) and any scope. Sometimes in our application, we need to get the last generated Identity column in order implement some logic based on last generated Identity value.

Figure 4:0– Demonstrating all functions to get the last generated Identity value within same scope/ any scope and Session.

In above figure, we can clearly figure out that we created two sessions of the user and inserted the records in OrderNew table from both the session and then we executed both three functions. While @@IDENTITY and @@IDENTITY_SCOPE() returns the Identity value generated for the session while IDENT_CURRENT(‘TABLENAME‘) returns the last generated Identity value across any session i.e. 2

Conclusion

SCOPE_IDENTITY and @@IDENTITY work on the same session.

IDENT_CURRENT(TABLENAME) across any session.