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
Asked In: Many Interviews |
Alert Moderator