April 17, 2010 at 8:14 pm
Comments posted to this topic are about the item What is the value of SCOPE_IDENTITY()
April 19, 2010 at 2:13 am
Well, that was an easy one 🙂
Good question to start the workweek.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2010 at 2:40 am
A more interesting result is from this:
DECLARE @sid int
DECLARE @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
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
April 19, 2010 at 3:01 am
Andrew Watson-478275 (4/19/2010)
A more interesting result is from this:
DECLARE @sid int
DECLARE @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
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
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).
April 19, 2010 at 3:21 am
woo-hoo! - easy for a change
April 19, 2010 at 4:15 am
Andrew Watson-478275 (4/19/2010)
A more interesting result is from this:
DECLARE @sid int
DECLARE @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
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
I believe this is simply an issue with the documentation, as BOL is much clearer on the @@IDENTITY function, it says:
... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...
I would expect the only significant difference between @@IDENTITY and SCOPE_IDENTITY to be their scope.
April 19, 2010 at 4:17 am
After getting the last 4 wrong, it was nice to get one right for a change. . . 🙂
yes, yes. I know. But I'm a .net coder, only recently decided to expand my knowledge to include some SQL
April 19, 2010 at 4:30 am
nigel. (4/19/2010)
Andrew Watson-478275 (4/19/2010)
A more interesting result is from this:
DECLARE @sid int
DECLARE @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
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...
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?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2010 at 5:04 am
da-zero (4/19/2010)
nigel. (4/19/2010)
Andrew Watson-478275 (4/19/2010)
A more interesting result is from this:
DECLARE @sid int
DECLARE @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
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...
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?
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 not
Maybe I should have included the preceding sentence from BOL which would have put it into context.
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.
April 19, 2010 at 5:29 am
nigel. (4/19/2010)
/* 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 not
Maybe I should have included the preceding sentence from BOL which would have put it into context.
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.
BOL on SCOPE_IDENTITY:
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.
In the example, since there are no GO statements to define batch boundaries, aren't all the statements in the same batch?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2010 at 6:06 am
da-zero (4/19/2010)
nigel. (4/19/2010)
In the example, since there are no GO statements to define batch boundaries, aren't all the statements in the same batch?
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.
April 19, 2010 at 6:22 am
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:
INSERT INTO @idtable (KeyData) VALUES('Test1')
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2010 at 6:57 am
da-zero (4/19/2010)
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:
INSERT INTO @idtable (KeyData) VALUES('Test1')
Hmm, looks like we need another opinion, 🙂
April 19, 2010 at 7:15 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 19, 2010 at 7:24 am
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply