Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which one to use among Ident_Current,Scope_Identity and @@Identity? Expand / Collapse
Author
Message
Posted Monday, September 29, 2008 6:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, January 13, 2014 1:31 AM
Points: 606, Visits: 335
Hi All,

In one of the stored procedure, I am inserting a record into table t1 which is having identity column as PK. I used Ident_Current('t1') to get the identity value.

It was working fine in production, but recently I found Ident_Current is not giving expected result.

Please guide me which one will give last inserted identity value among Ident_Current, Scope_Identity and @@Identity.
Post #577658
Posted Monday, September 29, 2008 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,852, Visits: 2,011
if you want to get the value just inserted into the table after your INSERT, and you know that the INSERT in your stored procedure only adds 1 row to the table then I'd say use SCOPE_IDENTITY() function immediately after your INSERT statement. IDENT_CURRENT() will not always give you what you expect because there could be multiple people adding records to the same table, so you wouldn't necessarily get the identity value you added to the table. The problem with @@Identity is that it can return the identity value from a different table than what you're expecting. Books Online will tell you that SCOPE_IDENTITY() limmits what it returns to the same stored procedure, trigger, function, or batch.

If your INSERT statement can add multiple rows to the table, then you can use the OUTPUT clause of the INSERT statement to put the entire list into a table variable.
Post #577715
Posted Monday, September 29, 2008 7:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:19 PM
Points: 33,094, Visits: 15,201
Chris has it correct, scope_identity is what you want.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #577719
Posted Monday, September 29, 2008 3:56 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 4,576, Visits: 8,342
Chris Harshman (9/29/2008)
If your INSERT statement can add multiple rows to the table, then you can use the OUTPUT clause of the INSERT statement to put the entire list into a table variable.

Not in SQL2000
Post #578026
Posted Monday, September 29, 2008 9:39 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, January 13, 2014 1:31 AM
Points: 606, Visits: 335

Thanks for the reply. I will go with scope_identity.
Post #578062
Posted Wednesday, November 5, 2008 3:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 9:34 AM
Points: 20, Visits: 55
I am new to sql server and I need your suggestions:
which one is the best for PK generation(@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT ). If i have transactions like this.
begin tran
Insert into customer(id,fname,lname) (4,'sameer','dutts')
@cid=SCOPE_IDENTITY --cid will be 4
Insert into address (adrsid,customerid,'addressvalue')(1,4,'dutts')
end tran
for such transactions what identity variable we should use.
Does begin tran end tran makes the current scope to return the correct identity value.Pls let me know asap.
Post #597731
Posted Wednesday, November 5, 2008 9:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 21, 2010 5:56 AM
Points: 21, Visits: 73
Hello Man,

Just imagine there are two tables named Tbl1, Tbl2 those contains identity field, right. Think, here you have a trigger stuff that will execute when ever an insert operation happened on Tbl2, that time from mach table(Inserted) ‘s news value will insert into Tbl1 got!!
Come back to your doubt,
If you are using Scope_Identity , by the name itself we know that is limited to its current scope and in our case that will return Tbl2’s last identity value, but actually that last identity updating happened on Tbl1 by the trigger
This identity value holds @@Identity System Variable.

@@Identity - his is not limited to the current scope
Scope_Identity – limited to the current scope

I think you got his light things.

Anoop.Sakthidharan
Post #597886
Posted Wednesday, June 5, 2013 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 12:22 PM
Points: 1, Visits: 3
Assume that there are a lot of people (e.g. 1,000+) are inserting records simultaneously into the database, will using SCOPE_IDENTITY (or any identity) right after the insertion return the correct PK? Would there be a chance that one personal will get a PK generated by another person since we have so many users inserting data into the database.
Post #1460351
Posted Wednesday, June 5, 2013 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 13,124, Visits: 11,960
chaijxiong (6/5/2013)
Assume that there are a lot of people (e.g. 1,000+) are inserting records simultaneously into the database, will using SCOPE_IDENTITY (or any identity) right after the insertion return the correct PK? Would there be a chance that one personal will get a PK generated by another person since we have so many users inserting data into the database.


Note, this thread is 5 years old.

You should check out the online documentation and the previous responses in this thread. SCOPE_IDENTITY is limited to the current context.

Check out this link. It explains the possibilities very clearly and concisely.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse