Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2005
    Posts
    33

    Unanswered: Regarding Identity column value retreival

    Hi,

    I am working on a .NET project for order manangement system. There will be multiple users accessing the application simultaneously. We have primary key columns as identity columns in most of our tables in our database. When we create new records, in our SPs we are doing an insert and to get the value of the Primary Key immediately after the insert, we are retrieving the last inserted identity value and using it in the rest of the procedure.

    Which one is the best to retrieve the value of the record just inserted - @@Identity, Scope_Identity OR Ident_Current

    We should have the right column value considering there will be multiple inserts in the tables precisely at the same, say, micro second. There should be no overlaps where the id value generated for one user/session etc should not be retrieved for another user or session etc.

    Please let us know what is the best to use in our condition.

    Thanks
    oursmp

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could switch to GUID values and then not have to worry about retrieving the last inserted primary key...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on, blindman, of course the GUID is an alternative, but sheesh, if the database has already been created and stored procs written, then your advice is spectacularly useless

    usually you offer good suggestions, but in terms of actually implementing this one, you are dreaming in technicolour

    and if you were just kidding, use a coupla smileys, would ya?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually Rudy, I don't buy that... If a surrogate key is used the way that I think surrogate keys should be used (purely abstract widgets used to show relationships within a database), there would be little if any impact in changing from an identity to a GUID.

    Granted that many developers use SK values in all kinds of inappropriate ways (including even showing them to users ), their poor behavior shouldn't impact the data modeler from making what should be a perfectly safe change to the schema.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I didn't see anything in his post that indicated the design was frozen, so I offered the suggestion as an alternative approach.

    And here are some smilies:


    Please save these and apply them to any of my posts wherever you deem appropriate.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you guys are both nuts

    so from now on, unless the original poster says "by the way, my database design is frozen" you feel it's okay to suggest a change that will have a huge implementation cost?

    have either of you guys ever ripped the identity columns out of all tables in an existing database and replaced it with a GUID?

    were you the ones explaining the necessity of doing this in the corner office to the guy in the suit responsible for assigning the developer resources to do all the regression testing on applications affected by the change?

    "little if any impact" my left testicle

    come on, get real
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    you guys are both nuts
    Likely, but irrelevant to the conversation.

    Quote Originally Posted by r937
    so from now on, unless the original poster says "by the way, my database design is frozen" you feel it's okay to suggest a change that will have a huge implementation cost?
    Yup. Unless they state otherwise, I have no problem suggesting an alternate design. The cost of saying no is $0 (American. Not sure what the Canadian exchange rate is...).

    Quote Originally Posted by r937
    have either of you guys ever ripped the identity columns out of all tables in an existing database and replaced it with a GUID?
    Not a big issue, depending upon how far along the development is, which we don't know.

    Quote Originally Posted by r937
    were you the ones explaining the necessity of doing this in the corner office to the guy in the suit responsible for assigning the developer resources to do all the regression testing on applications affected by the change?
    How do you know they already have applications based upon this design? Maybe they do, maybe they don't.

    Quote Originally Posted by r937
    "little if any impact" my left testicle
    Again discussing nuts, which as I already pointed out is irrelevant. How much impact your left testicle has is a matter best discussed with your doctor.

    Oops. Almost forgot:
    Last edited by blindman; 02-21-05 at 12:10.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how about one of you brilliant DBAs (amongst any category of which, brilliant or otherwise, i do not count myself) getting off my case and actually attempting to answer the original question --

    "Which one is the best to retrieve the value of the record just inserted - @@Identity, Scope_Identity OR Ident_Current"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Chill out Rudy! We didn't get on your case.

    I don't claim to know everything, and since I almost always use GUIDs instead of Identity, I don't feel I'm the best person to answer that question.

    Bad weekend?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, my weekend was excellent

    lotsa smartaleck posts today, that's all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hey, at least we keep bumping this to the top, so maybe some brilliant DBA will come across it and give the guy an answer.

    I may not be brilliant, but at least you think my advice is spectacular!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    In case ourspt is still looking for an answer, I'd suggest Scope_Identity() is the best answer (lack of brilliance notwithstanding).

    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my eyes!!! hey, at least nobody has used big honking red letters yet

    this is from another of the forum sites i hang out at --

    http://www.tek-tips.com/viewthread.cfm?qid=870259


    scope_identity turns out to be the one

    brilliant as usual, maxA

    <grin />
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I actually have changed the datatype from INT with an identity property to GUID for an entire database. The total effort on my part was 38 minutes to write and test a script. The total time to implement was about six hours because I missed two of the pseudo-foreign key columns that some naughty geek had added without a formal declaration. For a change that affected almost every row in a 62 Gb database, I didn't think that was too bad!

    Granted that this was an exception rather than the rule. We only had three folks that could access the database at the ad-hoc command level, and they clearly understood that you didn't mess with the PK columns except in a JOIN operation. While everyone ought to have these choices, not everyone does!

    -PatP

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh man, unless this 62GB database had 6200 evenly distributed tables in terms of rows, - you must be smoking something we all want to try out here Oh, and of course it must have had NO PK-FK relationship as well, because I can bet you that just writing a script for dropping, altering, and then re-adding PK-FK, even with no data AT ALL, - would have taken you longer than that on a medium size production system (150-250 tables, that's my medium size, what's yours? )...unless you used something like DBArtisan of course...then you're as lazy as I am...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •