Article

Difference between @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT in SQL Server

15 Mar 2018 Kamal Pratap
0 Comments 961 Views



Sometimes we need to get last inserted identity for any table. There are three properties to get last inserted identity.

 

@@IDENTITY: It's returns the last identity value generated for any table in the current session, across all scopes (i.e. global scope). We need to use this property whenever we don’t have any other functions or triggers that run automatically.

SCOPE_IDENTITY(): It's returns the last identity value generated for any table in the current session and the current scope(i.e. local scope). We need to use this property whenever we need to get last or newly inserted identity in table.

IDENT_CURRENT('table_name'): It's returns the last identity value generated for a specific table in any session and any scope (i.e. global scope). We need to use this property whenever we get the last inserted identiry of specified table.

Example :

CREATE TABLE Table1 (Id INT IDENTITY)
CREATE TABLE Table2 (Id INT IDENTITY(100,1))
-- Trigger to execute while inserting data into Table1 table
GO
CREATE TRIGGER TRGINSERT ON Table1 FOR INSERT
AS
BEGIN
	INSERT Table2 DEFAULT VALUES
END
GO

SELECT * FROM Table1  -- It will return empty value
SELECT * FROM Table2  -- It will return empty value
INSERT Table1 DEFAULT VALUES

SELECT @@IDENTITY  -- It returns value 100 this was inserted by trigger

SELECT SCOPE_IDENTITY()  -- It returns value 1 this was inserted by insert query in Table1

SELECT IDENT_CURRENT('Table2') -- It returns value inserted in Table2 table

Output :

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment