SQL Server @@IDENTITY, SCOPE_IDENTITY() & IDENT_CURRENT

A SQL Server IDENTITY column is used to generate key values automatically based on a provided seed and increment value.

Syntax:

IDENTITY [(seed, increment]

• Seed: is the value of the first row loaded into the table.
• Increment: is the incremental value added to the identity value of the previous row.

Download: SQL Server Identity functions script 

Quick revision: SQL IDENTITY functions

• The @@identity function returns the last identity created in the same session.
• The scope_identity() function returns the last identity created in the same session and the same scope.
• The ident_current(name) returns the last identity created for a specific table or view in any session.

Session: The session is the database connection.

Scope:  The scope can be a current query,  stored procedure, function and trigger.

Now, let’s start with an example

1. Create two tables using IDENTITY seed and increment

CREATE TABLE employee_1
(
ID INT IDENTITY (1,1) PRIMARY KEY,
empName nvarchar(50)
)

CREATE TABLE employee_2
(
ID INT IDENTITY (10,1) PRIMARY KEY,
empName nvarchar(50)
)

2. IDENT_CURRENT

• Now check current Identity value of above created two tables

SELECT IDENT_CURRENT('employee_1') AS employee_1_CurrentID
GO

SELECT IDENT_CURRENT('employee_2') AS employee_1_CurrentID
GO

• Insert few records under both tables

INSERT INTO employee_1 (empName) VALUES ('Maxi'),('Alen'),('Rosh'),('Tom')

INSERT INTO employee_2 (empName) VALUES ('Harry'),('Getten'),('Rock'),('Matt')

• Let’s check the current identity values again to using IDENT_CURRENT function

As expected, function returns the last identity created for a specific table or view in any session.

• • Output with current session id: 59

• • Output with other session id: 63

3. @@IDENTITY

• Let’s check the value of last identity for @@IDENTITY function

As expected function returns the last identity created in the same session.

• • Same Session Output

• Other Session Output, return null

4. SCOPE_IDENTITY

• Let’s check the value of last identity for SCOPE_IDENTITY function

It is similar to @@IDENTITY function, returns the last identity created in the same session.

• • Output in same session:

• Output in other session:

What is main difference between @@IDENTITY & SCOPE_IDENTITY() ?

If you have a trigger on the table and you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

• Create trigger on First table

CREATE TRIGGER employee_1_Trigger ON employee_1 FOR INSERT
AS
BEGIN
INSERT into employee_2(empName) values('Jerry')
END

• Insert record into first table

INSERT into employee_1(empName) values('Wong Lee')

• Now execute both function together

SELECT @@IDENTITY AS Last_IDNT 
Go
SELECT SCOPE_IDENTITY() AS ScopeID 
GO

• Final output

Hope you clear the difference between @@IDENTITY & SCOPE_IDENTITY().

Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Recommended SQL Server Post:

REPLICATE() function in SQL Server

SQL Server Configuration Functions

Query Optimization Technique in SQL Server

SQL Commands

SQL Keys

Alter table statement in SQL Server