How To Get Last Identity Inserted Value In SQL Server

IDENTITY property in SQL Server creates an identity type column. It generates auto-incrementing values in table by defining seed and increment values, works much like SEQUENCE object in SQL Server and Oracle. However, IDENTITY property is table dependent and SEQUENCE object works independently from the table.

Often we require to get last identity inserted value in SQL server. Multiple functions and methods are used to achieve this.

• SCOPE_IDENTITY
• @@IDENTITY
• IDENT_INSERT
• OUTPUT Clause

This post will walk you through different ways to get last identity inserted value. Helping to use the most recommended practice, to prevent breaking database code in future.

Here are various examples to get last identity inserted value in SQL Server. First, let’s create a Student and Student_History table with ID as an IDENTITY column.

USE tempdb;

DROP TABLE IF EXISTS Student;

CREATE TABLE Student
(
    ID        INT IDENTITY(1,1) PRIMARY KEY
   ,Name      VARCHAR(255) NOT NULL
   ,Standard  TINYINT
);
GO

DROP TABLE IF EXISTS Student_History;

CREATE TABLE Student_History
(
    ID        INT IDENTITY(1,1) PRIMARY KEY
   ,Name      VARCHAR(255) NOT NULL
   ,Standard  TINYINT
);
GO

1 SCOPE_IDENTITY & @@IDENTITY Function in SQL Server

SCOPE_IDENTITY is most recommended function to get last identity inserted value in SQL Server. It will return a value on the same scope and same session.

Let’s do a simple insert on Student table and to get last identity inserted value.

use tempdb;
GO

INSERT INTO Student VALUES ('Subhash', 12);

SELECT SCOPE_IDENTITY() as ScopeIdentity;
SELECT @@IDENTITY as [@@IDENTITY];

SELECT * 
FROM   Student;
GO

Result is as expected. We can see the correct value “1” is returned by both the functions.

Let’s Create following trigger to understand the difference between SCOPE_IDENTITY and @@IDENTITY function in SQL Server.

Use tempdb;
GO

DROP TRIGGER IF EXISTS TRG_INSERT_Student_History;
GO

CREATE TRIGGER TRG_INSERT_Student_History ON Student
AFTER INSERT
AS
BEGIN
        INSERT INTO Student_History VALUES ('Test', 10);
END

In following query we’ve made an insert on Student table which activites a trigger TRG_INSERT_Student_History. To insert a test record in StudentHistory table having ID identity column.

Use tempdb; 
GO

INSERT INTO Student VALUES ('Bhagat', 12);
GO

/* Identity Value "2" expected, same scope and same session. */
SELECT SCOPE_IDENTITY() as LastIdentityValue;

/* Instead of "2" Identity value from Student table it returns "1" from Student_History table considering any scope and any session because trigger was fired Due to above insert statement. */
SELECT @@IDENTITY as LastIdentityValue;

In above example, even though the last Identity insert was done on StudentHistory table through a trigger, SCOPE_IDENTITY functions returns correct value i.e. “2” from Student table whereas @@IDENTITY function returns “1” from StudentHistory table considering any scope and same session.

Hence it’s recommended to use SCOPE_IDENTITY function to get last identity inserted value in SQL Server to avoid any conflicts of getting an identity value from any different scope or session.

2. IDENT_CURRENT(‘TableName’) Function in SQL Server

Use of IDENT_CURRENT function to get last identity inserted value of a specified table regardless of its scope or session. It may be potentially dangerous if we’ve issued an INSERT statement and trying to get last identity value generated by INSERT statement which might not be true in case some other session has run INSERT statement on the same table from another session thus it will consider later value. Use this cautiously.

USE tempdb;
GO

INSERT INTO Student VALUES ('Azad', 6);
SELECT IDENT_CURRENT('Student') as IdentCurrent;

3. OUTPUT Clause To Get Last Identity Inserted Value

Another recommended way to get identity value is OUTPUT clause. We may capture multiple identity values using OUTPUT clause which is not possible with SCOPE_IDENTITY function. Using this we can get last inserted values from INSERTED magic table.

Additional table variable is required to capture the values and later use them. We can’t use local variables to store OUTPUT clause values.

USE tempdb;
GO

DECLARE @StudentIdentValues TABLE
(
	ID INT
);

INSERT INTO Student 
OUTPUT inserted.ID INTO @StudentIdentValues (ID)
VALUES ('Tilak', 10), ('Lakshmibai', 6);

SELECT *
FROM   @StudentIdentValues;

Summary

SCOPE_IDENTITY and OUTPUT clause is recommended way to get last identity inserted values in SQL Server.

You may read more useful post on SQL Server from tech-recipes.