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!!!

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2