SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by sdorris-1150234 / What is the value of SCOPE_IDENTITY() / Latest Posts

RE: What is the value of SCOPE_IDENTITY()

vignesh 38804 — Thu, 10 Jun 2010 01:52:59 GMT

nice question

RE: What is the value of SCOPE_IDENTITY()

nigel. — Tue, 20 Apr 2010 01:59:49 GMT

[quote][b]Hugo Kornelis (4/19/2010)[/b][hr]Tough question. We can't really look in the minds of the developers who originally wrote the feature, so we can only speculate.[/quote]Very true[quote]And frankly, I see little reason to do an INSERT with an IDENTITY, and then first do another INSERT before testing SCOPE_IDENTITY, ...[/quote]Yes, it is a strange thing to do in the first place.[quote]Given the similarlty to @@IDENTITY pointed out elsewhere in this discussion, that might very well even be the originally intended behaviour.[/quote]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.

RE: What is the value of SCOPE_IDENTITY()

Paul White — Mon, 19 Apr 2010 22:20:53 GMT

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.[url]http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value[/url]

RE: What is the value of SCOPE_IDENTITY()

sdorris-1150234 — Mon, 19 Apr 2010 15:49:34 GMT

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.

RE: What is the value of SCOPE_IDENTITY()

Clive Chinery — Mon, 19 Apr 2010 15:33:42 GMT

An excellent question! - Thank you!

RE: What is the value of SCOPE_IDENTITY()

Hugo Kornelis — Mon, 19 Apr 2010 11:04:21 GMT

[quote][b]nigel. (4/19/2010)[/b][hr]Hugo,Thanks for the input. Which would you say is correct, the implementation as it stands or BOL?[/quote]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.

RE: What is the value of SCOPE_IDENTITY()

SQLRNNR — Mon, 19 Apr 2010 09:49:54 GMT

Thanks for the Question

RE: What is the value of SCOPE_IDENTITY()

Andrew Watson-478275 — Mon, 19 Apr 2010 09:17:17 GMT

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).

RE: What is the value of SCOPE_IDENTITY()

nigel. — Mon, 19 Apr 2010 09:12:25 GMT

Hugo,Thanks for the input. Which would you say is correct, the implementation as it stands or BOL?

RE: What is the value of SCOPE_IDENTITY()

Hugo Kornelis — Mon, 19 Apr 2010 07:34:24 GMT

[quote][b]nigel. (4/19/2010)[/b][hr]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.[/quote]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.

RE: What is the value of SCOPE_IDENTITY()

sdorris-1150234 — Mon, 19 Apr 2010 07:24:39 GMT

We had a .Net application that kept getting a null reference returned from a stored procedure. Since SCOPE_IDENTITY() was used after the INSERT, we assumed the value was OK until more in-depth debugging pointed to the primary key.Hopefully, even the simple questions serve a purpose. I'm certainly more cautious about making assumptions while debugging problems.

RE: What is the value of SCOPE_IDENTITY()

webrunner — Mon, 19 Apr 2010 07:15:53 GMT

I was so happy to get a question right for the first time in 5 tries - even if I did realize it was relatively easy once I looked closely at the SCOPE_IDENTITY function name. :-)This is a good basic question and a good starting point for further learning about SQL variable scope.Thanks,webrunner

RE: What is the value of SCOPE_IDENTITY()

nigel. — Mon, 19 Apr 2010 06:57:05 GMT

[quote][b]da-zero (4/19/2010)[/b][hr]I interprete it as:SCOPE_IDENTITY returns the value of the last identity value inserted in the same scope. Since the scope is the whole batch, it doesn't matter if the identity inserted was in the last statement or not.This statement inserts an identity value and it belongs to the same batch, so SCOPE_IDENTITY shouldn't return NULL:[code="sql"]INSERT INTO @idtable (KeyData) VALUES('Test1')[/code][/quote]Hmm, looks like we need another opinion, :-)

RE: What is the value of SCOPE_IDENTITY()

Koen Verbeeck — Mon, 19 Apr 2010 06:22:43 GMT

I interprete it as:SCOPE_IDENTITY returns the value of the last identity value inserted in the same scope. Since the scope is the whole batch, it doesn't matter if the identity inserted was in the last statement or not.This statement inserts an identity value and it belongs to the same batch, so SCOPE_IDENTITY shouldn't return NULL:[code="sql"]INSERT INTO @idtable (KeyData) VALUES('Test1')[/code]

RE: What is the value of SCOPE_IDENTITY()

nigel. — Mon, 19 Apr 2010 06:06:03 GMT

[quote][b]da-zero (4/19/2010)[/b][hr][quote][b]nigel. (4/19/2010)[/b][hr]In the example, since there are no GO statements to define batch boundaries, aren't all the statements in the same batch?[/quote]They are yes, but I'm not sure how this is relevant to the issue of why SCOPE_IDENTITY returns NULL in this scenario?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.

RE: What is the value of SCOPE_IDENTITY()

Koen Verbeeck — Mon, 19 Apr 2010 05:29:10 GMT

[quote][b]nigel. (4/19/2010)[/b][hr]/* lots of quoting here */I don't think so. The SCOPE_IDENTITY (or @@IDENTITY) function will return the value appropriate to the most recent INSERT statement whether that statement affects a table with an IDENTITY column or notMaybe I should have included the preceding sentence from BOL which would have put it into context.[b][i]After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.[/i][/b][/quote]BOL on SCOPE_IDENTITY:[b][i]Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.[/i][/b]In the example, since there are no GO statements to define batch boundaries, aren't all the statements in the same batch?

RE: What is the value of SCOPE_IDENTITY()

nigel. — Mon, 19 Apr 2010 05:04:20 GMT

[quote][b]da-zero (4/19/2010)[/b][hr][quote][b]nigel. (4/19/2010)[/b][hr][quote][b]Andrew Watson-478275 (4/19/2010)[/b][hr]A more interesting result is from this:[code]DECLARE @sid intDECLARE @table Table (KeyID int primary key,KeyData varchar(5))DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))INSERT INTO @idtable (KeyData) VALUES('Test1')INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')SET @sid = SCOPE_IDENTITY()select @sid[/code]This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.[/quote][b][i]... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...[/i][/b][/quote]But in the example there is a statement that affects a table with an identity constraint, so SCOPE_IDENTITY (or @@IDENTITY ) should have a value different from NULL, right?[/quote]I don't think so. The SCOPE_IDENTITY (or @@IDENTITY) function will return the value appropriate to the most recent INSERT statement whether that statement affects a table with an IDENTITY column or notMaybe I should have included the preceding sentence from BOL which would have put it into context.[b][i]After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.[/i][/b]

RE: What is the value of SCOPE_IDENTITY()

Koen Verbeeck — Mon, 19 Apr 2010 04:30:14 GMT

[quote][b]nigel. (4/19/2010)[/b][hr][quote][b]Andrew Watson-478275 (4/19/2010)[/b][hr]A more interesting result is from this:[code]DECLARE @sid intDECLARE @table Table (KeyID int primary key,KeyData varchar(5))DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))INSERT INTO @idtable (KeyData) VALUES('Test1')INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')SET @sid = SCOPE_IDENTITY()select @sid[/code]This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.[/quote][b][i]... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...[/i][/b][/quote]But in the example there is a statement that affects a table with an identity constraint, so SCOPE_IDENTITY (or @@IDENTITY ) should have a value different from NULL, right?

RE: What is the value of SCOPE_IDENTITY()

Cadavre — Mon, 19 Apr 2010 04:17:43 GMT

After getting the last 4 wrong, it was nice to get one right for a change. . . :)[size="2"]yes, yes. I know. But I'm a .net coder, only recently decided to expand my knowledge to include some SQL[/size]

RE: What is the value of SCOPE_IDENTITY()

nigel. — Mon, 19 Apr 2010 04:15:46 GMT

[quote][b]Andrew Watson-478275 (4/19/2010)[/b][hr]A more interesting result is from this:[code]DECLARE @sid intDECLARE @table Table (KeyID int primary key,KeyData varchar(5))DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))INSERT INTO @idtable (KeyData) VALUES('Test1')INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')SET @sid = SCOPE_IDENTITY()select @sid[/code]This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.[/quote]I believe this is simply an issue with the documentation, as BOL is much clearer on the @@IDENTITY function, it says:[b][i]... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...[/i][/b]I would expect the only significant difference between @@IDENTITY and SCOPE_IDENTITY to be their scope.

RE: What is the value of SCOPE_IDENTITY()

Tom Brown — Mon, 19 Apr 2010 03:21:15 GMT

woo-hoo! - easy for a change

RE: What is the value of SCOPE_IDENTITY()

Hugo Kornelis — Mon, 19 Apr 2010 03:01:31 GMT

[quote][b]Andrew Watson-478275 (4/19/2010)[/b][hr]A more interesting result is from this:[code]DECLARE @sid intDECLARE @table Table (KeyID int primary key,KeyData varchar(5))DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))INSERT INTO @idtable (KeyData) VALUES('Test1')INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')SET @sid = SCOPE_IDENTITY()select @sid[/code]This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.[/quote]Hi Andrew,That is indeed very interesting. I checked Books Online, but it definitely says "last identity value inserted in the same scope", not "identity value inserted in last INSERT statement in the same scope", yet the latter is exactly what happpens. If you add SELECT SCOPE_IDENTITY(); directly after each INSERT statement, you'll see the correct result (1) after the first INSERT, but after the second it reverts to NULL.I also searched on the connect site to see if this bug has already been reported, but that appears not to be the case. I recommend you to report this as a bug. If you then post the URL here, I'll vote on it (and probably some others too).

RE: What is the value of SCOPE_IDENTITY()

Andrew Watson-478275 — Mon, 19 Apr 2010 02:40:21 GMT

A more interesting result is from this:[code]DECLARE @sid intDECLARE @table Table (KeyID int primary key,KeyData varchar(5))DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))INSERT INTO @idtable (KeyData) VALUES('Test1')INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')SET @sid = SCOPE_IDENTITY()select @sid[/code]This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.

RE: What is the value of SCOPE_IDENTITY()

Koen Verbeeck — Mon, 19 Apr 2010 02:13:21 GMT

Well, that was an easy one :-)Good question to start the workweek.

What is the value of SCOPE_IDENTITY()

sdorris-1150234 — Sat, 17 Apr 2010 20:14:39 GMT

Comments posted to this topic are about the item [B]What is the value of SCOPE_IDENTITY()[/B]