Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

What is the value of SCOPE_IDENTITY() Expand / Collapse
Author
Message
Posted Monday, April 19, 2010 3:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, Visits: 154
An excellent question! - Thank you!
Post #906380
Posted Monday, April 19, 2010 3:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 22, 2011 7:33 AM
Points: 289, Visits: 433
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.
Post #906388
Posted Monday, April 19, 2010 10:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:20 PM
Points: 11,192, Visits: 11,093
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #906491
Posted Tuesday, April 20, 2010 1:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
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.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #906603
Posted Thursday, June 10, 2010 1:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 5, 2010 10:56 PM
Points: 230, Visits: 17
nice question
Post #935204
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse