Is it safe to Insert and use the inserted id in a single transaction

Question

Is it safe to Insert and use the inserted id in a single transaction Like below. What I want to know is when will be the new entry to the Table1 will be created? After committing the transaction or before?

will that Id will can be able to access in the Table2?

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO  dbo.Table1([Name]) 
    values('Name')

    DECLARE @Id int
    SET @Id =  SCOPE_IDENTITY()

    INSERT INTO dbo.TableNam2(UId,FeatureId) 
    VALUES(NEWID (), @Id)
COMMIT

END TRY

BEGIN CATCH

    ROLLBACK

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

You should try and see. My understanding is that it's fine to do this. The only thing is that if you rollback, your ID will not be used. This will leave gaps in your identities. But this is not a problem unless you rely on there being no gaps — Nick.Mc, Sep 21 '17 at 04:48
It's perfectly fine, the new entry will be created on the insert. It's just that it will be deleted if you call the rollback. — Marc Guillot, Sep 21 '17 at 05:35

score 3 · Accepted Answer · edited Sep 21 '17 at 08:36

Yes, it is safe because you have used SCOPE_IDENTITY(). That's not the case if you had used @@IDENTITY, which could pick up a new identity from a trigger insert on dbo.Table1, for instance.

The new identity is generated as soon as the insert is executed, regardless of whether the transaction is committed or rolled back.

Yes, you can use that value to insert into dbo.Table2. No problems there.

A couple of pointers from my experience:

As @Nick has pointed out, this does consume an identity value that will never appear in the table if either insert fails for any reason, possibly leaving gaps. Same goes for inserts that fail outside of a transaction. Identity is never guaranteed to be contiguous.
If someone down the road was to inadvertently add another statement immediately after your first insert without noticing the use of SCOPE_IDENTITY() you may get unexpected results. I like to attach the SET statement to the end of the insert to make you think twice about inserting something before you have fetched the new identity

Try something like this:

declare @Id int

insert into dbo.Table1([Name]) values ('Name') 
set @Id = SCOPE_IDENTITY()

-- Code continues here ...

Read Further - What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

Is it safe to Insert and use the inserted id in a single transaction

1 Answers1