SQL Clone
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 (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5046 Visits: 4010
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64499 Visits: 19117
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10646 Visits: 11970
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 (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

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

Group: General Forum Members
Points: 24 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
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26538 Visits: 17557
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 Modens 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