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

Identity Column in SQL Server

What is Identity Column?

An identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an Auto Number field in SQL Server.

An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

If you mark a column as identity column need not require to supply value for that column.

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')

Now you can see Person_id value is generated automatically.

Supply explicit values using Identity_Insert

Now delete one record from above 

delete from tblPerson where Person_id=1

(1 row affected)

Now lets try to insert new record.

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

values ('Rohit Kumar','M')

You can see new value assigned instead of assigning 1.

Now lets try to assign Person_id value manually

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Throws error -

Cannot insert explicit value for identity column in table 'tblPerson' when IDENTITY_INSERT is set to OFF.

Now set Identity_insert

set IDENTITY_INSERT tblPerson ON

Now try to insert same record:  

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Reset the Identity Column

If you have deleted all the rows in a table and you want to reset the identity column then use below command

delete from tblPerson

DBCC CHECKIDENT('tblPerson',RESEED,0)

INSERT INTO tblPerson([Name], [Gender])
values
('John Doe','M'),
('Kimi Sharma','F')

You can also see:

How to find last generated identity column value in SQL Server