How to Get the Identity of an Inserted Row in SQL Server

In SQL Server, we can use an Identity column, which will automatically increment the ID value in a table. The Identity column is a unique, sequential, and non-null value for each row in a table. The identity value is auto-incremented using the IDENTITY command. There could be situations when this auto-incremented value of the Identity column may be required.

In this article, we will discuss the different options available to get the identity value of the inserted row.

Identity of an Inserted Row

The identity of an inserted row is the last identity column value in a table which was automatically generated by the sql server database system when a new row is added to a table that has an identity column with auto increment set with initial and increment values like the below example.

EmployeeID Int NOT NULL IDENTITY (1,1)

In the above example, the EmployeeID column is created as an identity column to auto increment by 1 with an initial value starting from 1. When a new row is created in the table the EmployeeID will be 1 and it will increment the value by 1 after each row insert subsequently.

Getting the Identity Value from the Inserted Row

When an identity column is incremented while a new row is inserted, the same value may be required in the SQL to update or insert in another column or for any other processing of data like in Store procedure to update the EmployeeID value from employees table into a Managers table like the example below:

INSERT into Employees Values ('Raja Sekharan','.NET, AZURE, JAVA')
INSERT INTO Managers   Values (SCOPE_IDENTITY(), 'Raja Sekharan', 'NA')

Here the EmployeeID identity column value is updated to Managers table EmployeeID column when a new row is inserted into the Employees Table.

To get the identity value in SQL Server, there are 3 different options or system functions as below:

1. SCOPE_IDENTITY()
2. @@IDENTITY
3. IDENT_CURRENT(‘TableName’)

Let us look into these options in details with examples.

1. SCOPE_IDENTITY()

This function in SQL Server return the last identity value generated for any table in the current scope and session

INSERT into Employees Values ('Joel Thomas','.NET, AZURE, JAVA')
Select SCOPE_IDENTITY()  as LastEmployeeId

Output:

Below is the Identity value using Scope_Identity() for the last employee inserted

Identity value using scope_identity()

When we run a select statement in Employee table the data will look like the below

Select * from employees where employeeID > 15

2. @@IDENTITY

The @@IDENTITY also returns the last identity value from a table insert with identity column. This value returned is from the current session but from across all scopes. When there are other statements that insert rows with identity column in the same session, it returns the last identity value.

INSERT into Employees Values ('Raja Mohan','.NET, AZURE,JAVA')
INSERT INTO Managers   Values (@@IDENTITY, 'Raja Mohan', 'NA')
Select @@IDENTITY  as LastIdentityValue

Output:

Below is the Identity Value using @@Identity

Identity value using @@identity()

When we query both employees and managers table, below is the data

employees and managers table data

3. IDENT_CURRENT(‘TableName’)

This function returns the last Identity value inserted for a specific table across any scope and session. This will be helpful to get the last identity value of a table when multiple inserts are done by different users or when multiple inserts to different tables are done in a session and need to access the identity value of a specific table as given in the example below. The @@IDENTITY or SCOPE_IDENTITY() will return only the identity value from the last inserted value from last insert query.

INSERT into Employees Values ('Mohana Kumar','.NET, AZURE')
INSERT INTO Managers   Values (@@IDENTITY, 'Mohana Kumar', 'Techical Manager')
Select IDENT_CURRENT('Employees')  as LastEmployeeID

Output:

Below is the Identity value of employee table using the IDENT_CURRENT

Identity value using Ident_Current

When we query both employees and managers table, below is the data:

employees and managers table data

Conclusion

Getting the inserted value from an Identity column is useful in many situations and this can done using the system functions instead of going to the table and finding the last identity value. But there can incorrect identity values returned when there are multiple inserts in a table or across many tables. So use the right system function to get the identity value based on session and scope of the insert done. Also note that the identity value is returned only from a Identity Column set with auto increment option.