Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can SCOPE_IDENTITY return null when @@IDENTITY does not?

Tags:

After executing an insert, I either select SCOPE_IDENTITY or @@IDENTITY.

SCOPE_IDENTITY returns null but @@IDENTITY does not.

I don't understand how this is possible.

Can you think of a reason why this happens?

like image 523
pn. Avatar asked Mar 05 '10 19:03

pn.


People also ask

What is the difference between Scope_identity and @@ Identity?

If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table.

What is the use of @@ identity and Scope_identity?

The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session.


1 Answers

here is one example of how SCOPE_IDENTITY() will be null but @@IDENTITY will have a value:

insert into a table with no identity, that table has an insert trigger that then inserts into a history table with an identity. SCOPE_IDENTITY() will be null (no identity in the local scope), but @@IDENTITY will report the identity from the trigger.

FYI, there is a known bug with SCOPE_IDENTITY(): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

Your best bet with identities is to use OUTPUT INTO, it can capture a set of IDs and is not subject to the SCOPE_IDENTITY() bug:

declare @x table (tableID int identity not null primary key, datavalue varchar(10)) declare @y table (tableID int, datavalue varchar(10))  INSERT INTO @x values ('aaaa') INSERT INTO @x values ('bbbb') INSERT INTO @x values ('cccc') INSERT INTO @x values ('dddd') INSERT INTO @x values ('eeee')   INSERT INTO @x     (datavalue)     OUTPUT INSERTED.tableID, INSERTED.datavalue     --<<<<OUTPUT INTO SYNTAX     INTO @y                                         --<<<<OUTPUT INTO SYNTAX SELECT     'value='+CONVERT(varchar(5),dt.NewValue)     FROM (SELECT id as NewValue from sysobjects where id<20) dt     ORDER BY dt.NewValue   select * from @x select * from @y 
like image 70
KM. Avatar answered Sep 22 '22 17:09

KM.