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