What is the difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT ?

@@IDENTITY ==> Is a system function that returns the last-inserted identity value for any table in the current session, across all scopes.

SCOPE_IDENTITY() ==> Returns the last identity value inserted into an identity column for any table in the current session and in the same scope.

IDENT_CURRENT ==> Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

GO

-- create a main table

CREATE TABLE test_main(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- insert some records

INSERT INTO test_main(name) VALUES('A'),('B'),('C')

GO

-- create child table

CREATE TABLE test_child(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- create a trigger on main table to insert the reccords into child table

CREATE TRIGGER test_trigger ON test_main FOR INSERT

AS

BEGIN

INSERT INTO test_child SELECT name FROM INSERTED

END

GO

-- check the identity on main table

SELECT * FROM test_main

-- result

1 A

2 B

3   C

GO

-- now insert records into main table and check the identity

INSERT INTO test_main(name) SELECT 'D'

-- now check the below result

select @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT('test_main')

-- result

1 4 4