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 can do this by setting IDENTITY_INSERT to ON for the specific table. Once IDENTITY_INSERT is on, you can insert a value to the IDENTITY column.
Note that you need to set IDENTITY_INSERT to OFF when you are done.
I have presented an example here:
I have query like below
declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121’
)
select @str_CustomerID= scope_identity()
after execution it returns null in my parameter
Is there an identity column on the table?
May we use scope_identity() for selecting the last inserted value in different server.
hi i am kapil. I am new on this site. this is a very good article.
hi , i m amol. It is nice site to solve my problems. Thank’s
can’t we get the last updated id of a table?? thanx
As we get in the insertion , can’t we get the id (primary key) of a table when it’s updated??
I only need to get the last updated primary key. Thanx
It is possible Only if you have a timestamp column
only need to get the last updated primary key.
Thanks
Please answer ASAP.
Hi Pinal,
i am adding multiple rows in a table using XML. How do i get @@identity of each row inserted?
Please help……
Also please tell me is is feasible or not.
In SQL server 2005 and 2008 you can use a new feature: OUTPUT clause. You will get right values without mistakes.
Here is the link.
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017
This feature really helps to solve all Identity column issues along with other benefits.
Hi…Habydij Raja
We cant use scope_identity() for selecting the last inserted value in different server.
Hi! can anyone help me..
Im creating a CAI (computer aided instruction) on VB6
and i just want to view a last record of ID and increment it by 1? can anyone please help me..?
Thanks a lot… :D
Use scope_identity() and assign it to the variable
hi,
can you answer the query for this:
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The table “Product” includes information about the maker, model number, and type (‘PC’, ‘Laptop’, or ‘Printer’). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table “PC” is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM – ram (in Mb), hard disk drive capacity – hd (in Gb), CD ROM speed – cd (for example, ‘4x’), and the price. The table “Laptop” is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size – screen (in inches). For each printer in the table “Printer” it is told whether the printer is color or not (color attribute is ‘y’ for color printers; otherwise it is ‘n’), printer type (laser, jet, or matrix), and the price.
* Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).
Result set: model with higher number, model with lower number, speed, and RAM
thank u
very helpful
hi
i have a doubt pls clarify it….
i created a table with an identity column say a student table , student id as IDENTITY(100,1)… so at each insert the student id will be incremented by 1 starting from 100 and goes on 101,102……….
actually what i need is the student id should be like this “S100,S101,S102″…. is this possible using IDENTITY….
how can i get this …..
thanks in advance…
Deepan
Very Nice Pinal
Shyam
This is the third time I met your blog while searching sth on google so I wanted to thank you this time. Very informative article. It helps me to solve my problem.
Hi Pinal,
Your article is just awesome.
I have a small query regarding the performance of the Three Clauses used above:
Can you please tell which one is better from performance point of view
@@IDENTITY / SCOPE_IDENTITY() / IDENT_CURRENT(‘tablename’)
Waiting for your reply.
Regards
Gaurav Girdher
i think best perfarmance provide by
IDENT_CURRENT(‘tablename’)
how to get the last inserted record in sql server
Possible only if you have any columns with datatype datetime, rownumber to get this. Also in relational DB, there is no concept of first or last record