Capture the Last Identity Value from Table Identity Column

There are 3 ways for us to get the last identity value from an identity column of a table.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Let’s learn how to use and distinguish them from examples below.

1. 　　Create a table with an Identity Column, insert 5 rows data into the table, and then get the last Identity value from the table

IF ISNULL(OBJECT_ID('Name'),0) <> 0 DROP TABLE [Name]
GO
CREATE TABLE [dbo].[Name](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL
) ON [PRIMARY]

GO

INSERT INTO Name(Name) values('Jobin')
GO 5

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
    @@IDENTITY AS [@@IDENTITY],
    IDENT_CURRENT('Name') AS [IDENT_CURRENT],
    IDENT_SEED ( 'Name' ) AS[IDENT_SEED],
    IDENT_INCR ( 'Name' ) AS [IDENT_INCR];
GO

Result:
SCOPE_IDENTITY　　@@IDENTITY　　IDENT_CURRENT　　IDENT_SEED　　IDENT_INCR
-------------- 　-----------　　-------------　　----------　　----------
5                5             5               1             1

 　　

　　2.　　 Create two table, both of them have an Identity column, and create a trigger on table1 to insert the same data into table2 when inserting, then use @@IDENTITY and SCOPE_IDENTITY to get the last Identity value from the session.

IF ISNULL(OBJECT_ID('Class1'),0) <> 0 DROP TABLE [Class1]
IF ISNULL(OBJECT_ID('Trg_Class1_After_Insert'),0) <> 0 DROP TRIGGER Trg_Class1_After_Insert
IF ISNULL(OBJECT_ID('Class2'),0) <> 0 DROP TABLE [Class2]
CREATE TABLE [dbo].[Class1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Class2](
    [ID] [int] IDENTITY(1,5) NOT NULL,
    [Name] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TRIGGER Trg_Class1_After_Insert
ON Class1
AFTER INSERT AS 
BEGIN
    DECLARE @Name varchar(50)
    SELECT @Name = Name FROM inserted
    INSERT Class2(Name) VALUES (@Name)
END

GO

INSERT INTO Class1(Name) values('Jobin')
GO 5

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
    @@IDENTITY AS [@@IDENTITY],
    IDENT_CURRENT('Class1') AS [IDENT_CURRENT_Class1],
    IDENT_INCR ( 'Class1' ) AS [IDENT_SEED_Class1],
    IDENT_SEED ( 'Class1' ) AS [IDENT_INCR_Class1],
    IDENT_CURRENT('Class2') AS [IDENT_CURRENT_Class2],
    IDENT_INCR ( 'Class2' ) AS [IDENT_SEED_Class2],
    IDENT_SEED ( 'Class2' ) AS [IDENT_INCR_Class2];
GO

Result:
SCOPE_IDENTITY　　@@IDENTITY　　IDENT_CURRENT_Class1　　IDENT_SEED_Class1　　IDENT_INCR_Class1　　IDENT_CURRENT_Class2　　IDENT_SEED_Class2　　IDENT_INCR_Class2
--------------　　----------　　--------------------　　-----------------　　----------------- 　 --------------------   -----------------　　-----------------
5                21            5                 　　　1                 　 1                　　21               　　　  5                 　1

Hope this can help you.