Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Which one to use among Ident_Current,Scope_Identity and @@Identity?


Which one to use among Ident_Current,Scope_Identity and @@Identity?

Author
Message
Raghavendra-499237
Raghavendra-499237
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 351
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.
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2687 Visits: 3289
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36156 Visits: 18751
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
My Blog: www.voiceofthedba.com
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5844 Visits: 11406
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
Raghavendra-499237
Raghavendra-499237
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 351
Thanks for the reply. I will go with scope_identity.
Aur_Ek_SQL
Aur_Ek_SQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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.
Anu-923127
Anu-923127
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 73
Hello Man,w00t

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
chaijxiong
chaijxiong
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search