SQL Server SCOPE_IDENTITY() vs @@IDENTITY

Question

Would there be any benefit of not using SCOPE_IDENTITY() and switching to @@IDENTITY? For the area I'm talking about is part of an install script that sets up a database for our customers. It's inserting a record in one table and using the identifier key from that table and inserting it into a foreign key into another. We are doing this twice.

We seem to have a rare condition in which the 2nd time this happens, we are inserting the id from the first insert into the 2nd table for both passes, causing issues with the data. There is a chance that something else altogether is causing this, but my lead seemed to zeroed in on the SCOPE_IDENTITY() as possibly being the culprit.

Declare @TheId int

Insert into dbo.TableName (Name) Values ('xxxx')
Select @TheId = SCOPE_IDENTITY()

-- some code here that uses @TheId
-- ...

Insert into dbo.TableName (Name) Values ('yyyy')
Select @TheId = SCOPE_IDENTITY()

-- some code here that uses @TheId
-- at this point, we may have the condition that SCOPE_IDENTITY() still has the value before that 2nd insert...

All other [identity-related things](https://stackoverflow.com/q/1920558/11683) are less specific. `SCOPE_IDENTITY()` is correct, assuming, of course, that the `insert` and the subsequent `select` are indeed in the same scope. — GSerg, Sep 25 '20 at 19:09
Are you sure the second `insert` doesn't fail? Unless you have try/catch, execution will continue and the second `SCOPE_IDENTITY()` will return the previous value. — GSerg, Sep 25 '20 at 19:21
WHy assign the value of `@TheID` again, if you don't want its value to change? — Thom A, Sep 25 '20 at 19:25
The second insert did not fail as the record was found in the database. We are getting the value of @TheId to be the id of the table that was inserted. I left out a bunch of the code as it's proprietary, — Kelly Jayne Anderson-McConnell, Sep 25 '20 at 19:29
I become more confused with what the problem actually is and what your expectations are. What happens when you do `set @TheId = null;` right before the second `insert`? — GSerg, Sep 25 '20 at 20:01

Joel Coehoorn · Accepted Answer · 2020-09-27T02:27:37.260

The only way scope_identity() could have the prior id value in this context is if the INSERT statement does not create any rows. In that situation, @@IDENTITY isn't gonna fix anything. In fact, @@IDENTITY is less specific, and therefore could only hope to make things worse.

What you can do is use a different variable for the second insert. Or, you could set @TheId back to NULL before the second insert runs. In this way, you'll be able to tell if something went wrong. @@rowcount is also useful for this.

I did see this in the comments:

"The second insert did not fail as the record was found in the database."

I put it to you perhaps the record was already in the database, before the code ran. Moreover, if there is a constraint on the table this could be the reason why the insert fails.

SteveC · Answer 2 · 2020-09-26T00:32:29.987

Within the scope of the proc or script the @TheId created by the first insert is not same object as the @TheId created by the second insert. While it's possible to reuse variables it's not a good practice imo when it comes to multiple DML statements within a code block. In this script I add TRY/CATCH and SET XACT_ABORT ON to ensure a complete rollback of all DML statements within the block.

Something like this

set nocount on;
set xact_abort on;

begin transaction
begin try
    Insert into dbo.TableName (Name) Values ('xxxx');
    if @@rowcount=1
        begin
            Declare @Id1 int = SCOPE_IDENTITY();

            -- some code here that uses @Id1
            -- ...
        end
    else
        throw 50000, 'The first insert failed', 1;

    Insert into dbo.TableName (Name) Values ('yyyy');
    if @@rowcount=1
        begin
            Declare @Id2 int = SCOPE_IDENTITY();

            -- some code here that uses @Id2
            -- ...
        end
    else
        throw 50000, 'The second insert failed', 1;

    commit transaction
end try
begin catch
    /* put error handling here */

    rollback transaction
end catch

score -1 · Answer 3 · answered Sep 28 '20 at 15:10

-1

Thanks everyone for the help. We will likely go with creating a new variable for the 2nd insert.

answered Sep 28 '20 at 15:10

Kelly Jayne Anderson-McConnell

23
1
7

SQL Server SCOPE_IDENTITY() vs @@IDENTITY

3 Answers3

Linked