What is @@IDENTITY in SQL? When we should use and scope of @@IDENTITY?

What is @@IDENTITY in SQL?

The @@IDENTITY is a system function which returns the last inserted identity value.

All the @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT are similar functions because all are return the last inserted value into the table’s IDENTITY columns.

The @@IDENTITY and SCOPE_IDENTITY return the current session last identity value but the SCOPE_IDENTITY returns the current scope value.

What is the scope of @@IDENTITY?

In the @@IDENTITY, there are no any limitations for a specific scope.

Syntax: - @@IDENTITY   

Return Type: - numeric (38, 0)

For example as,

-- USE OF @@IDENTITY
INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn)  
 VALUES ('IT-PROGRAMING', 'This is a Prrogrammer!', 1, 'Anil Singh', GETDATE());  
GO  
 SELECT @@IDENTITY AS 'COL_IDENTITY';  
GO

The Use of SCOPE_IDENTITY :-

-- DECLARE RETURN TABLE
DECLARE @Return_Table TABLE (Code varchar(10)
 ,Message varchar(100), ID varchar(100))

--INSERT CONTACT TYPE
INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn)  
 VALUES ('IT-PROGRAMING', 'This is a Prrogrammer!', 1, 'Anil Singh', GETDATE());  

--USE SCOPE_IDENTITY
IF (@@ERROR <> 0)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'An error occurred in updating the customer.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'OK', 'SUCCESS', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
GO

I hope you are enjoying with this post! Please share with you friends!! Thank you!!!