Explain about @@IDENTITY..

Answer:

Unlike SCOPE_IDENTITY function, this @@IDENTITY function will returns the value which is the last generated identity in the current session.
A session may contain one or more sessions.
An important point to note is that, if at a time, two users using two different connections connected to SQL server inserts two rows with identity column in a table, then each of them will get the value they have just inserted.

Example showing the difference between SCOPE_IDENTITY and @@IDENTITY

Let’s check how Scope_Identity() function got impacted in both the scopes.

CREATE TABLE [dbo].[DimUser]

(

	[userId] int IDENTITY(1,1) ,

	[userName] varchar(100) NULL

)

 

GO



CREATE TABLE [dbo].[DimUser1]

(

	[userId1] int IDENTITY(1,1) ,

	[userName1] varchar(100) NULL

)

GO

Let’s create a SP named “sp_InsertData” as:

CREATE PROCEDURE sp_InsertData

AS

BEGIN

    INSERT  INTO [dbo].[DimUser1]

    VALUES ('xyz'),

    ('pqo'),

    ('abc');

END

[dbo].[DimUser1] is another table created similar to [dbo].[DimUser] stated above.

Now, let’s execute our sample query:

INSERT  INTO [dbo].[DimUser]

VALUES ('Arun'),

('John'),

('Bunty'),

('Stenly'),

('Kumar');

GO

EXEC sp_InsertData

 

SELECT @@IDENTITY AS [@@IDENTITY], 

       SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Result:

  @@IDENTITY  SCOPE_IDENTITY

  3	      5