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
MyPK is the identity value of the MyTable2. it does not contain in the MyTable1. here i need current inserting row identity. just removing braces gives Invalid column name ‘MyPK’
There is an important difference between IDENT_CURRENT versus SCOPE_IDENTITYand @@IDENTITY when referencing the IDENTITY value within an INSERT statement. See below for an example
CREATE TABLE [dbo].[t_TestIdentity](
[TestID] [bigint] IDENTITY(1,1) NOT NULL,
[TestIDCopy] [bigint] NULL,
[TestText] [nvarchar](32) NULL,
CONSTRAINT [PK_TestID] PRIMARY KEY CLUSTERED
([TestID] ASC)
)
GO
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (IDENT_CURRENT(‘[dbo].[t_TestIdentity]’), ‘Test1’)
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (SCOPE_IDENTITY(), ‘Test2’)
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (@@IDENTITY, ‘Test3’)
SELECT * FROM [dbo].[t_TestIdentity]
TestID TestIDCopy TestText
1 1 Test1
2 1 Test2
3 2 Test3
Tanx Paul. This is what i needed.
i have a table with around 10,000 records. one of the column in the table is having identity. i have only deleted, but not truncated all the records in the table to keep my identity value. without having any records in the table, i need to find out my latest identity value. Can you let me know how to find the latest identity value without having any records in the table. Please update your comments on this?
Hello, I am using a MS Access front end that uses ODBC to connect to a MS SQL Server 2005 with replicated tables.
Some times I had to remove and add again the merge database for identity problems.
there is any workarround to change the @@identity for scope_identity() in default to avoid the problems ??
I am not writing the SQL code, then I cant chose one or the other.
Inserting records in a vincled form is showing a register diferent than the inserted into de database and is producing errors.
Hi Pinal,
Thanks a lot for wonderful site.
This is related to @@Identity, I want get latest value inserted in table.
But as per this thread @@identity does not give latest value inserted in table
While searching on other sites i got exact and i am sure correct solution, below is the link
Solution 1:
CREATE TABLE HumanResources.Employees(
Id int IDENTITY(1,1) NOT NULL,
— Other columns ,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (Id ASC)
)
———————————————————————
DECLARE @InsertedRows AS TABLE (Id int)
DECLARE @NewId AS INT
INSERT INTO HumanResources.Employees
( /* column names */)
OUTPUT Inserted.Id INTO @InsertedRows
VALUES (/* column values */)
SELECT @NewId = Id FROM @InsertedRows
Solution 2:
This might helpful..
ex:
Create
( Id int IDENTITY(1,1) NOT NULL,
colname varchar(20)
)
Begin Tran
INSERT into (ColName) values (xxxxxx)
Select max(id) from
End
Solution 2 can work for small kind of data.
Thanks
In continuation with above thread..
@@identity give correct value when i run some on code on SQL editor, but when i did same thing on existing Stored procedure then it give different value.
This is because @@Identity returns the last identity value inserted in any table or session of the database and so in a high (or even medium) concurrency environment @Identity will give you unexpected values
Thanks
good article, every time ur only the best. If i get doubt regarding sql then i do search question+by ur name only Iam great fan of u sir. Thank you.
thanks for u r article. help to resolve my problem………..
Thank you again. I’ve just bought 2 of your books. As always, you were the go to guy. One addition to this for anyone who doesn’t know. To set the return value to an output parameter do this:
set @NameOfYourIDParamter = (select SCOPE_IDENTITY())–return new row ID
Hi Pinal,
I have one question.
i want to insert identity column into next table.
table1(col1(identity) pk, name)
table2(childname,col1 fk)
in a sinlge transaction stored procedure i want to insert two one records in both the table . like one record in table 1 and get the seed number and insert into second table and i want to return the seed value. so that i can catch it using output paramater direction from .net
like
declare @seedvalue bigint
insert table1(name)values(‘basker’)
set @seedvalue=@@identity
insert table(childname,col1)values(‘baskerchild’,@seedvalue)
return @seedvalue
is this correct?
can i use DML after setting @@identity seed value to any variable ?
please reply
thanks in advance
I am passing table name as parameter in my stored procedure and I want to get that table’s last inserted row’s Id. How can I do that? I want to get last row’s id through SELECT IDENT_CURRENT
I am using stored procedure and I am passing table name as parameter. SO in this case how could I use SELECT IDENT_CURRENT(‘tablename’)?
Hi,
In the para for SELECT SCOPE_IDENTITY() you mention below text at the end of the para.
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.
Can User defined function create any identity ? If yes please let me know how. I think UDF can’t perform any DML operation.
Please give up expensive “commercial” stuff. A small picture and a link suffices.
Thanks!
this any of identity not work with synonyms. how can i do with synonyms table..?
We have a table with columns source_id(primary key) and root_source_id which is NOT NULL. We would like root_source_id to have a default value of source_id.
@@identity and SCOPE_IDENTITY() give us erroneous ID values from other table, as our DB is usually accessed by a farm of 80 machines together on different tables. Using DB.IDENT_CURRENT(‘tablename’) seemed to best so far for us as we can specify the table. But even this has failed sometimes as it may still fetch IDs from other sessions. Do let me know if trigger is the only way (does not really work for us because we have to make root_source_id as nullable).
I wonder why it is so difficult to get the identity value available in a “current” insert statement?
CREATE TABLE dbo. (
[source_id] INT IDENTITY(1,1) primary key,
[root_source_id] int NOT NULL default(IDENT_CURRENT(‘source’)),
[source_path] nvarchar(max) NOT NULL
);
go
ALTER TABLE dbo.
ADD CONSTRAINT [source_source_FK4]
FOREIGN KEY ([root_source_id])
REFERENCES ([source_id]) ON UPDATE NO ACTION ON DELETE NO ACTION
go
Hello Pinal,
Thanks for good article. I am not master in Sql Server. I am tying to understand your article, but one thing I couldn’t understand, that is “While @@IDENTITY is limited to the current session, it is not limited to the current scope.”, Here what is meaning of Current Session, and Current Scope? I am facing these words first time.
Thank you Uncle Dave
Hi Dave, I need your help below, I want the @@identity to be added in a column after insert, the problem is if multiple users are calling the query, the unwanted identity value is inserted. Does adding Begin Tran helps so that the identity should be within the current transaction?
Create table tblTransactions(dctransno pk(auto-increment), vcDescription, dcTransNoCopy)
Insert into tblTransactions values(@vcDescription);
set @identity = @@IDENTITY
Update tblTransactions set dcTransNoCopy= @identity where dcTransNo = @identity;
Thanks in advance.
Hello Pinal,
Thanks for the explanation.
In my project, i am building a sql dataadapter insert command which combines the insert statement and the select scope_identity statement.
When running this combined statement in sql server 2005 database, the identity column does not get updated and in sql server 2008 database, a new column – Column1 is appended and the identity value is set, the original identity column stays empty.
Whereas, when i use ‘SELECT @@IDENTITY as [identity column name]’ after the insert statement, in sql server 2005 the identity column gets updated and in sql server 2008, the same ‘Column1’ gets added and the identity value is set.
could you please help me understand this behavior and advice me if i should continue with @@IDENTITY ?
Thanks a ton.