Home » SQL Server » SQL Server @@IDENTITY, SCOPE_IDENTITY() & IDENT_CURRENT

SQL Server @@IDENTITY, SCOPE_IDENTITY() & IDENT_CURRENT

SQL Server Functions

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
Current Identity

Current Identity

  • 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
Identity Current_1

Identity Current_1

    • Output with other session id: 63
Identity_Current

Identity_Current



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
@@Identity

@@Identity function same session

  • Other Session Output, return null
@@Identity

@@Identity function in other session

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:

Scope_Identity

  • Output in other session:
Scope_Identity

Scope_Identity

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')
Insert Record into table

Insert Record into table

  • Now execute both function together
SELECT @@IDENTITY AS Last_IDNT 
Go
SELECT SCOPE_IDENTITY() AS ScopeID 
GO
  • Final output

@@IDENTITY VS SCOPE_IDENTITY()

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

Leave a Reply