How to find last generated identity column value in SQL Server

Please go through the below before proceeding:

Identity Column in SQL Server

For retrieving identity column values first create a table. 

Create a table using Identity Key:

CREATE TABLE tblPerson (

    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,

    [Name] varchar(50),

    [Gender] CHAR(1)

);

INSERT INTO tblPerson([Name], [Gender])

values

('John Doe','M'),

('Kimi Sharma','F')

We can get last generated Identity values in two ways

Using function SCOPE_IDENTITY() and global variable @@IDENTITY

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('tblPerson')

Output: 2

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

You can find the difference Here:

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example