Explain about @@IDENTITY..

 Posted by Bharathi Cherukuri on 7/24/2012 | Category: Sql Server Interview questions | Views: 1280 | Points: 40
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 

Comments or Responses

Login to post response