SQL SERVER - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record

March 25, 2007 by pinaldave

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.

Source: Pinal Dave (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Cursor, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology | 49 Comments

49 Responses to “SQL SERVER - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record”

on June 26, 2007 at 2:09 am rahul

very fine Article
Thanx
Regards
Rahul
on July 9, 2007 at 1:45 am raksha

can we use scope_identity() for selecting the last inserted value in different server..
on July 9, 2007 at 1:47 am pinaldave

No.
on July 9, 2007 at 2:07 am raksha

actually i hv to find out max value of one pramary key field.it will always a last inserted value.

so if my connection is opened for one server..then i am not able to find out the last inserted value with scope_identity() for that server.

so for this is there any other way .. or we have to use “select MAX(ID)”
on July 12, 2007 at 8:10 pm Ramgopal.M

Is there any way to retrieve the latest value of a field inserted in a table (apart from primary key field)…….if yes plz let me know…..
on July 16, 2007 at 10:08 am Oskar Diaz

Can i use the IDENT_CURRENT to get the last record updated? I am trying to implement logging on a table with a trigger but I need to get the ID of the record that was updated.
on August 2, 2007 at 2:27 pm Apteka internetowa

Thank you for the useful informations.
on August 2, 2007 at 2:29 pm Apteka internetowa

Can we use scope_identity() for selecting the last inserted value in different server.
on August 6, 2007 at 3:44 am Ana

hi,

Is it possible to find out the list of lattest used stored procedure in sql server, say for eg the list of 10 stored procedure being used in a page.

Is it possible to retrieve from the sysobjects ?
on August 6, 2007 at 12:47 pm Jacob Hume

Thanks, this is a great piece of information. I’m more familiar with MySQL, and having a function I’m used to using makes working with MS SQL much nicer!
on August 8, 2007 at 6:53 am sulaiman

I want to get the last inserted identity value of a table in a different server.

i want to use like :
select ident_current(’server2.db1.dbo.table’)

if i give like this
select ident_current(’db1.dbo.table’)
i am getting the result.
on August 30, 2007 at 9:34 pm rajuneo

I need to retrieve identity column value from the below query:

INSERT INTO table-name(col1, col2) VALUES(val1, val2); SELECT SCOPE_IDENTITY()

using C + MS-SQL through ODBC
Please find the psuedo code that I have used:

hstmt = SQLAllocHandle
odbc_stmt = “INSERT INTO STUDENT (NAME, DIVISION) VALUES (?, ?); SELECT SCOPE_IDENTITY()”
SqlPrepare(hstmt, odbc_stmt)
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, &std_name, …
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, &std_division, …
SQLBindCol(hstmt, 3, SQL_C_SSHORT, &std_id, ….

SQLExecute(hstmt)
SQLFetch(hstmt)

————

Here ID is an identity column for the student table.

I am able to insert the row. But identity column value for the inserted row is not getting retrieved to std_id variable after SQLFetch.

Please help me to find out the problem?

thanks in advance….
on August 31, 2007 at 3:03 am pinaldave

rajuneo,

Your code is not in T-SQL. I will be not able to help you out.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 11, 2007 at 1:35 pm Mestophales

Awesome just what I needed - thank you
on October 11, 2007 at 7:28 am RTW

Here is the problem, I create records using the identity number from the membership. I have checked the number is the same going in the db which is MS SQL 2005 however I can not retrieve records using the identity number, I have tried everything in grid view.

For example:

I have a drop down box list all users on selection it poplulates the grid view control.

However the grid view control does not populate I have even tried this manually pasting the identity number in but it returns no results. I am missing something here.

I write in VB VS2005 MS SQL 2005

Can you point me in the right direction to make this work? I am lost.
on October 19, 2007 at 8:20 am mircea

How do I get the id of the last inserted row if I don’t have an identity column? Of course, @@identity returns NULL in this case.

Thank you
on October 23, 2007 at 2:28 am rajuneo

Hi all,

I want to post the solution for my previous question (Index: 12). After digging for a lot of time, found missing piece from the code.
The key function is SQLMoreResults. It should be called just before SQLFetch.
The main purpose of SQLMoreResults is to find out if we have more than result set. We can always combine multiple queries in a single statement. In that scenario, we may expect multiple result sets. So to traverse from one result set to other, we require to call SQLMoreResults function.

thanks, rajuneo
on November 5, 2007 at 7:21 pm yogesh

INSERT INTO (SELECT Lists.ListCode ,Lists.ListName
FROM Lists)
VALUES (’ListCode’,'ListName’)

plz tell me this statement run!!!!!!!!!
on November 14, 2007 at 5:21 pm Sandeep

Can we use bigint for identity field?
on November 16, 2007 at 9:36 pm c

What can I use to get the last Identity value like @@IDENTITY but over 2 connections.

I need to do multiple inserts and my program works with @@IDENTITY as the primary key the first time the program runs.

When I run it a 2nd time, I get the violation of primary key constraint and the statement has terminated error.

Please help!
Thanks,
Claudia
on November 16, 2007 at 9:37 pm c

Also Ident_Current(’tableName’) does not work in SQL Server 7
on January 15, 2008 at 10:21 pm Martin

Thanks!
on January 24, 2008 at 12:38 pm Najam Khan

I run it but gives null value can u specify it.
on February 7, 2008 at 12:53 pm rajuneo

To 18: Yogesh:-

Your statement is wrong. If you want to insert data from one table into another, check this out:

insert into table2(select table2.t2id, t2name) select * from table1
on March 9, 2008 at 3:50 pm javid

hi
how to find Current row(Primary key)?
on March 19, 2008 at 11:54 am Prateek

Hi,
I have a query it will be a great help for me.

I have 3 tables

table1
( T1id identity (1,1) PK, Name)

table 2
(t2id PK, T1id, Address)

table3
(t3id PK,T1id,Phone)

Now I need to insert data like this

begin transaction
—multiple rows are inserted from xml insert in table 1
for i to 100
insert into table1 values(’adsf’)

next
for j to 100
—Similarly multiple rows are inserted from xml insert in table 2 and table 3
— but I cannot get the t1ID which is in table 1 because it has multiple identity now after xml insert

— so here in table2 and table 3 the t1ID cannot be retrieved from scope_Identity()
– so we need some what like array of scope_Identity()
next
commit transaction

on error
rollback transaction

— i think you got my point
– can you please reply me at my mail address provided

– Thank you in advance

Can you please solve this problem in sql server 2005
on March 22, 2008 at 2:09 am Chetana

good article
on March 27, 2008 at 12:55 am Alex Schafer

Great article.

One question though:

Can we use scope_identity() for selecting the last inserted value in different server?
on April 14, 2008 at 9:18 am Vishwanath

Dear Pinal,

Firstly I would like to thank you for this wonderful site.

Secondly I have a question to post.

What is the syntax to alter a table to add identity property (with seed and incremental values) to an already existing column in that table?
on April 14, 2008 at 9:22 am Vishwanath

Thanks in advance for any reply.
on April 25, 2008 at 1:31 am Wes

I have been running replicated servers using merge replication and SQL 2000 for about a year without issue. I have several sp’s that used @@Identity

I upgraded to SQL 2005 last week.

Guess what - those SP’s are now intermittently returning the identity of a table from an associated repl trigger.

Have you seen or heard of this occurring after an upgrade?

Thank goodness I stumbled onto your article and was able to fix all instances of @@Identity in my SP’s. (Now I have to replicate the sp’s manually off-hours….)

thanks
Wes
on April 29, 2008 at 2:44 pm shreya

I want to select the records from a table which are updated or added in the last one month (table doesn’t have any datetime field and I can’t change the structure). Is it possible to write a sql query for that?
on May 8, 2008 at 12:42 pm srinivas

to retreive the last entered record in table
on May 14, 2008 at 6:09 pm khushboo

very informative article……..thanks a lot…..

but it will be more easy to understand if examples had been put here………
on May 15, 2008 at 8:55 pm Vijay Anand Kannan

Hi Viswanath,

I dont think that you can add the Identity to the existing coloumn. You need to drop the table and recreate with Identity. You can add a new column with Identity in alter table.

- Vijay Anand Kannan
on May 17, 2008 at 1:39 pm Jaiveer

Hi, Pinal,
The Solution you have given seems very problem solving. I will try this for sure. My problem is this that: I have an web application, problem is that; when more than one user enterd data on the same time the data entered wrongly i.e. user 1 data save in user2 and user 2 data saves in itself(user2)
on May 30, 2008 at 2:22 am Mahee

Iam having a real strange issue with both the @@identity and the scope_identity(). The problem is running the same insert on two different machines with the exact DBs and the result on machine is as expected and on the other i get null values for both. unlike the examples quoted by the users here, iam using a select statement instead of values list to insert.
And Iam using SQL 2005 w/ SP2 on both the machines. (The backward compatibility levels for both are SQL server 2000 (80) if you need to know)

Here is what Iam doing:

INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ’somevalue’;
SELECT @@IDENTITY;

INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ’somevalue’;
SELECT Scope_Identity();

INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ’somevalue’;
SELECT Ident_Currenty(’TEMPTABLE’);

One machine gives me same value on all three statements, while the other gives me null on first two and gives me a value on the third.

Any insights will be appreciated.

- Mahee
on June 12, 2008 at 3:41 am scarlett

hi

Please help me out.

In my stored proc, I use @ident_current of a table(Table X) as a default value for my table variables (Temp Table)

Now Since Im creating a Table Variable, I want one of its columns to have a default value of the identity of the Table X , Now when creating this Table Variable, it will not accept
a variable as a default hence forcing me to use Ident_Current(Table X) as the default. I know that this can produce erroneous data as it applies to any session .. is there another way out? I cant use @@Identity as there are 2 more Table Variables created after the insertion of Table X - Hence will pick up wrong identity value ….
here’s my code
——————————————–

INSERT INTO TABLE X

DECLARE A TABLE A
INSERT INTO TABLE A

DECLARE TABLE B
INSERT INTO TABLE B

DECLARE C TABLE (COL1, COL2 INT DEFAULT IDENT_CURRENT( ‘TABLE X’ ),COL3)

————————————————————
on August 8, 2008 at 7:45 pm Hard2Get

The main problem is, I can’t get the query result from 2 different connection with different table.
But with the same connection/server, I can get the query result.

SELECT SERVER1enroll.ID
FROM SERVER1enroll
WHERE SERVER1enroll.ID NOT IN (SELECT SERVER2enroll.ID FROM SERVER2enroll)

And is there a shortcut for that SQLcode to make a faster query than that.

Can anyone solve that?
I’m using 2 different connection, Server1 on my PC, Server2 is on the other PC.

I’m using VB9
Can anyone share some code for development testing?
on September 10, 2008 at 3:26 pm suresh

how can i add identity coloumn to existing coloum through query
on September 17, 2008 at 3:03 pm santhosh

i have user id table i want retrive last user id from that table
i need sql query for pls help me
on October 4, 2008 at 2:56 pm sham

nice one .. thnk you
on October 16, 2008 at 3:15 pm Ayyappan

Can we have more than one identity column in a table?

if yes, please tell me how we can do that?

Thanks
on October 23, 2008 at 1:06 pm Rohit Hans

Thanks very much. It really helped me to understand difference between various options of identity.
on October 30, 2008 at 1:18 pm Joggee

My problem resolve. God Bless you.
I always have a first choice to search here.

Keep up the good work.

Joggee
on November 10, 2008 at 12:24 pm Bharathidasan

Hello sir,

i have 1 doubt. how can i retrieve last 10 records from a particular table.

pls i want answer for this
on November 18, 2008 at 9:50 am nofar

hello sir,
please send me the query of fetch 10 random rows from table,
on December 1, 2008 at 12:05 pm Seema

Good Article
on December 14, 2008 at 10:45 am Jai

Excellent, it’s very clear and understandable article.

Thanks