SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Reference : Pinal Dave (https://blog.sqlauthority.com)
230 Comments. Leave new
You saved me again. Your posts always provide better explanations than MS
Following SQL Query is to get the LAST INSERTED ID if the column is auto increment using iBatis and SQL SERVER.
— your insert statement
select @@IDENTITY as value
I think it’s important to note here that while @@IDENTITY and SCOPE_IDENTITY() appear to work the same in the context of the same scope, that only @@IDENTITY will work if you happen to be trying to reference the current identity value within the same insert statement that is creating the IDENTITY value. SCOPE_IDENTITY() will return a NULL value in this case. I know you will ask why you would ever want to do this, but i just had a case where i had to do this and SCOPE_IDENTITY() did not work.
I’m convinced with the use of SCOPE_IDENTITY until one scenario i found out that SCOPE_IDENTITY doesn’t return the desired value if Instead of Triggers is implemented on the desired table.
But luckily, the @@IDENTITY works with that way.
However, still can’t understand the logic behind the scenario.
(Scenario Notes:)
INSTEAD OF TRIGGERS => checks the other tables in terms of required data, then, reinserts all fields with updated data.
I also found similar issue, but @@Identity is also risky in case you are inserting a record in another table via trigger.
here you can use current_ident(tablename), but I have doubt, what happen if two persons try to insert a record in a same table and same time?
@Ieuan0928
Could you post the scenario please. That is odd that id did not work.
Hello,
I have the same question like raksha posted
on July 9, 2007 at 2:07 am
Can you please help us?
Thanks and Regards,
Sandhya P.
Good explanations.
In one of my application SCOPE_IDENTITY() returns 1, some times. Below is the Code
INSERT INTO CUSTOMER(…)
SELECT … FROM #temp_table
SET @id = SCOPE_IDENTITY()
Note: Here CUSTOMER table has a AFTER trigger.
Any Guess..?
Thanks for producing a nice short article that gave the answer to what I was looking for.
how i know latest updated and inserted records in sql table.
Hi,
SET @id = SCOPE_IDENTITY() was the solution.
Thank you.
Regards,
Sandhya M. Potdar.
Again good article….
SCOPE_IDENTITY() is useful when multiple user inserting data in same table at same time??
so that will get separate identity for each row inserted.
??
or will get mess up
No, SCOPE_IDENTITY does not get messed up when multiple users insert data. You will get the identity value generated by your session only.
Thankx a lot
Thanks a lot. Thearticle was very useful. Keep writing such articles as it is very useful for Developer community…
Hi,
I am inserting multiple record. i am getting only last record id.i need latest inserted record id.
SCOPE_IDENTITY() it’s givining only 1 record.
example: I inserted 3 record,i need 3 records id.
Create PROCEDURE [dbo].[OrganisationSelectedType_insert]
(
@prmOrgTypeID nvarchar(50),
@prmOrganisationID nvarchar(50),
@prmOrganisationName nvarchar(50)
)
AS
BEGIN
INSERT INTO [OrganisationTypes] ([OrganisationID],[OrganisationName],[OrgTypeID])
SELECT @prmOrganisationID,@prmOrganisationName, data FROM [Split] (@prmOrgTypeID, ‘;’)
SELECT SCOPE_IDENTITY() As savedId
END
please help.
Thanks
Thanks for useful info…
I needed this for a long time for improving efficiency of my applications.
Thanks again…
Thansk for this article.it help me to solve my problem
thanks
I have a table of 25 records. Client mistakenly deleted the 15th row. Can u able to insert the same row with same identity? If possible how?
Could U Plz help me ….