| Author |
Topic |
|
gdeconto
Posting Yak Master
107 Posts |
 Posted - 2004-02-17 : 10:49:42
|
| I am somewhat confused by the difference here (and the sql docs havent helped much). Any assistance appreciated:we're having a discussion regarding how to determine the auto-increment value of an insert and there is some concern over how to best get that value from within a stored procedure (ie to avoid any instances where we would get the wrong value).say we have a table with an autoincrement field:CREATE TABLE mytable(id int IDENTITY, tada varchar(10))and we have a sproc:CREATE PROCEDURE mysprocASINSERT mytable (tada) VALUES ('foo')GOif I want to know the value of id in mytable, would I not simply use @@identity immediately after the insert? that is the value I just inserted is it not??however, we have alternative opinions that state that we should be using ident_current wrapped in a transaction with the insert to guarantee we always get the "correct value". their concern is that @@identity only gets the last autoincrement value in the session whereas ident_current gets the last autoincrement value in the table.wouldnt that allow the stored procedure to return an autoincrement value that was created in a trigger or other stored procedure or by other users?? ie not the value the stored procedure created by its use of INSERT??Any help appreciated. |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-02-17 : 11:12:46
|
| consider scope_identityIt is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
 |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-02-17 : 11:20:31
|
| why, given the example scenario I provided, would I use scope_identity instead of @@identity and ident_current??ie why would it return the correct value or why would @@identity and ident_current return the wrong value?? |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-02-17 : 14:18:44
|
| I mentioned it because you said sql docs didn't help,and there is a concise arguement for SCOPE_IDENTITYthat overlaps @@identity.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-02-17 : 14:24:37
|
| Sitka, I was not disagreeing with you. I was asking "why" because I wanted more information on why you suggested it (ie because I do not understand the differences)ie can you explain further why you would choose scope_identity over @@identity? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 14:34:14
|
quote:
@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.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.
@@IDENTITY can be incorrect if ever a trigger is going to insert rows into that table. Somebody blogged about the three on SQLTeam a while back, you might want to check it out. Also search for @@IDENTITY and chadmat in the forums. chadmat (Chad is his name) has discussed the differences and problems here a few times.Tara |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-02-17 : 15:09:35
|
the "WHY" part is why the BOL demo of SCOPE_IDENTITY seemed so valuable, I'm not pleading a case one way or another.But If I was..... it would appear after reading the chilling definition of indent_current in BOL that ident_current isn't even intended for such a thing."Returns the last identity value generated for a specified table in any session and any scope." But as you show there may be sound methods or a desire to employ it like this. In fact to me it hints that one could build conditional processing of transactions to prove out a desired behavior of all of them. SCOPE_IDENTITY behaviour addresses it's own durability after someone applies a trigger to the table at a later date, or the @@identity value is coming back incorrect based on a current trigger.Still probably aren't great arguements for your entire scenerio and beyond what the BOL demo showsIt is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-02-17 : 15:12:12
|
| Thx Tara.one of the articles that Chad replied to had a very good, very simple example (hey, if I can understand it then it must be simple [grin]!!) that explained why @@identity would be a poor choice over scope_identitynow will continue to look for a good, simple example of if/why ident_current would be better in my example than scope_current.thx all. |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-02-17 : 15:15:45
|
| thx Sitka. appreciate the feedback. looks like your reply came back just before I sent mine. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-17 : 21:27:21
|
| Hmm, I have always been under the impression that @@IDENTITY gets the ID created by the last insert within the same scope...? Gotta do some thinking about this, I might get some messed up data then...gdeconto; would it be possible to paste the link to the examples you were referring to?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-02-18 : 11:01:31
|
| Hi Lumbagosure."@@IDENTITY returns the last identity value set. if you append a row to table "A" with an identity, but that append causes a trigger which adds a row to table "B" which ALSO has an identity column, it returns the identity from table "B". Which is probably not what you want." "SCOPE_IDENTITY always returns the identity from the current table and never from a trigger." |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
|
|
|
@@identity versus ident_current