What is the value of SCOPE_IDENTITY()

Question

What is the value of SCOPE_IDENTITY()

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

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

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: 11636 More actions · Answer 2

Hugo,

Thanks for the input.

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

Nigel
How to post questions

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 3

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: 281334 More actions · Answer 4

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: 64780 More actions · Answer 5

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 More actions · Answer 6

Clive Chinery

SSCrazy

Points: 2563

April 19, 2010 at 3:33 pm

#1154180

An excellent question! - Thank you!

sdorris-1150234 SSC-Addicted Points: 457 More actions · Answer 7

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: 150467 More actions · Answer 8

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nigel. SSChampion Points: 11636 More actions · Answer 9

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
How to post questions

vignesh 38804 Old Hand Points: 338 More actions · Answer 10

vignesh 38804

Old Hand

Points: 338

June 10, 2010 at 1:52 am

#1178482

nice question