Before discussing these three methods, you need to understand the concept of session and scope. Session means the current database connection in which a user interacting with a relational database through the use of SQL commands. A session may be established by either a front-end application or a direct connection to the database. Within the Scope of a SQL session, we can write queries to get data from the tables, manipulate data in the tables, with the help of a stored procedure, trigger, function, or batch. Every call to a SQL stored procedure executes in its own scope and if we call nested stored procedure then this call executes in a nested scope within the calling procedure's scope.

Now, let's discuss the differences between the three identity retrieval methods:

@@IDENTITY: When we run T-SQL @@IDENTITY after INSERT, SELECT INTO, or bulk copy statement then it returns the last identity value that is generated by the statement. If the statement did not insert any record in any tables, @@IDENTITY returns NULL. If you inserted multiple rows, SQL engine respectively generated multiple identity values, @@IDENTITY returns the last identity value generated in the current session but any scope.

SCOPE_IDENTITY(): Returns the last identity value generated into an identity column within the scope of the current session.

IDENT_CURRENT(): Returns the last identity value inserted in a specified table or view. The last inserted identity value can be for any session and any scope.

SCOPE_IDENTITY Example: Let’s create two tables tblSale and tblStock table as given below. 

CREATE TABLE tblPurchase(
  PurchaseId INT IDENTITY(1,1) PRIMARY KEY,
  ProductName VARCHAR(50),
  Quantity INT
)

GO

CREATE TABLE tblStock(
  StockId INT IDENTITY(1,1) PRIMARY KEY,
  ProductName VARCHAR(50),
  PurchaseQuantity INT
)
Now insert some data in these tables
        
INSERT INTO tblPurchase(ProductName,Quantity) VALUES ('Crackers',1000)
INSERT INTO tblPurchase(ProductName,Quantity) VALUES ('Rolls',100)
INSERT INTO tblPurchase(ProductName,Quantity) VALUES ('Cakes',500)

GO

INSERT INTO tblStock(ProductName,PurchaseQuantity) VALUES ('Bread',600)
INSERT INTO tblStock(ProductName,PurchaseQuantity) VALUES ('Pastry',300)
let's see the last generated identity for these tables using the below commands
        
SELECT IDENT_CURRENT('tblPurchase')
SELECT IDENT_CURRENT('tblStock')
The output of these commands


Now create an INSERT trigger on the tblPurchase table. 

CREATE TRIGGER T_UpdateStockOnPurchaseItem
ON tblPurchase
FOR INSERT AS
BEGIN
	INSERT INTO tblStock(ProductName,PurchaseQuantity)
	SELECT ProductName,Quantity FROM INSERTED
END
Once we insert any row on tblPurchase, it calls the defined trigger for inserting a row in tblStock table.
Let’s insert a record into tblPurchase table and see the output of both @@IDENTITY and SCOPE_IDENTITY() functions.
       
INSERT INTO tblPurchase(ProductName,Quantity) VALUES ('Sugar',5)
Output


You execute an insert command on a tblPurchase table, which fires an insert trigger that inserts a in the tblStock table. Immediately after, before you execute another command, another session does the same thing.

@@IDENTITY return the identity value=3 of the tblStock table, because the trigger on the tblPurchase table executes after inserted the record into the tblPurchase table, so the tblStock identity is the last identity generated in your session but any scope.

SCOPE_IDENTITY() will return the identity value=4 of the row you inserted into the tblPurchase table because that's the last identity value generated at this level of scope in your session

IDENT_CURRENT('tblPurchase') will return the last generated id in tblPurchase table and IDENT_CURRENT('tblStock') will return the last generated id in tblstock table regardless of any session.