Monday, July 13, 2015

SCOPE_IDENTITY() and @@IDENTITY in T-SQL

Imagine we wish to insert a record and then take the primary key for that record and use it to turn around and insert another record as suggested here. This could also be done like so:

BEGIN TRANSACTION setup
   INSERT INTO Note (WhateverId, Copy)
   VALUES (@WhateverId, 'hello world')
   DECLARE @NoteId int
   SELECT @NoteId=SCOPE_IDENTITY()
   INSERT INTO Affidavit (WhateverId, NoteId, Copy)
   VALUES (@WhateverId, @NoteId, 'hello lawsuit')
COMMIT TRANSACTION setup

 
 

...and like so too:

BEGIN TRANSACTION setup
   INSERT INTO Note (WhateverId, Copy)
   VALUES (@WhateverId, 'hello world')
   DECLARE @NoteId int
   SELECT @NoteId=@@IDENTITY
   INSERT INTO Affidavit (WhateverId, NoteId, Copy)
   VALUES (@WhateverId, @NoteId, 'hello lawsuit')
COMMIT TRANSACTION setup

 
 

What is the difference between the two? They both have a clipboard cache of sorts of the last primary key used but while SCOPE_IDENTITY() is restricted to the transaction at hand @@IDENTITY has no such restriction so two commits from two different sources happening simultaneously should be able to dirty up its behavior causing heartache.

No comments:

Post a Comment