What is the value of SCOPE_IDENTITY()

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    nigel. (4/19/2010)


    This is how I interpret the docs:

    SCOPE_IDENTITY will return the identity value from the most recent insert in the current scope. If the most recent insert was to a table that has no IDENTITY column then the value returned by SCOPE_IDENTITY will be NULL.

    Hi Nigel,

    While that is, obviously, exactly what SCOPE_IDENTITY does, it is absolutely NOT what Books Online says, and there is no way you can interpret the text in Books Online that way.

    This is exactly why I recommended Andrew to file this as a bug on the Connect site. Microsoft can then decide whether this is a documentation bug or a product bug, and fix either the docs or the product.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • nigel.

    SSChampion

    Points: 11620

    Hugo,

    Thanks for the input.

    Which would you say is correct, the implementation as it stands or BOL?

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4613

    I just tried it in SQL2000 (my original attempt was in 2008), and it does the same, so I'd guess this is what is meant to happen, and it's probably BOL at fault (or it's a really old bug).

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the Question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    nigel. (4/19/2010)


    Hugo,

    Thanks for the input.

    Which would you say is correct, the implementation as it stands or BOL?

    Hi Nigel,

    Tough question. We can't really look in the minds of the developers who originally wrote the feature, so we can only speculate.

    And frankly, I see little reason to do an INSERT with an IDENTITY, and then first do another INSERT before testing SCOPE_IDENTITY, so I really would not mind much if Microsoft decides to change the docs, not the implementation. Given the similarlty to @@IDENTITY pointed out elsewhere in this discussion, that might very well even be the originally intended behaviour.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Clive Chinery

    SSCrazy

    Points: 2563

    An excellent question! - Thank you!

  • sdorris-1150234

    SSC-Addicted

    Points: 457

    You're welcome! It gave me a headache one day trying to debug an application, so I thought I'd post the question.

    I've learned a great deal from this forum. It's amazing to me how even a simple question can generate a great discussion. That's why I keep coming back.

  • Paul White

    SSC Guru

    Points: 150442

    Given the number of inconsistencies and bugs, I'd be quite happy to see @@IDENTITY and SCOPE_IDENTITY removed from the product. I very much prefer the OUTPUT clause for this purpose.

    http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

  • nigel.

    SSChampion

    Points: 11620

    Hugo Kornelis (4/19/2010)


    Tough question. We can't really look in the minds of the developers who originally wrote the feature, so we can only speculate.

    Very true

    And frankly, I see little reason to do an INSERT with an IDENTITY, and then first do another INSERT before testing SCOPE_IDENTITY, ...

    Yes, it is a strange thing to do in the first place.

    Given the similarlty to @@IDENTITY pointed out elsewhere in this discussion, that might very well even be the originally intended behaviour.

    Agreed, given that the documentation for @@IDENTITY is quite clear on this it's odd (at the very least) that it's not covered in the SCOPE_IDENTITY docs.

  • vignesh 38804

    Old Hand

    Points: 338

    nice question

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply