3 ms·
If you execute this in a transaction (as you should whenever dealing with any two queries that alter state and logically follow each other), then neither case c
by moron4hire 13y ago
If you execute this in a transaction (as you should whenever dealing with any two queries that alter state and logically follow each other), then neither case creates a state in which some other transaction sees the first half of this sort of update complete without the second half. And if you don't, then it's not quite the same as DELETE+INSERT, because you haven't destroyed any data.
Also, even if one considered this "no better" than DELETE+INSERT, it has significant other benefits for other purposes. The history of data is worth it alone.
But you've made me consider the issue more, and I think perhaps a better way to run this would be:
INSERT ... -- same as before
DECLARE @ID int;
SELECT @ID = SCOPE_IDENTITY();
UPDATE the_table
SET InvalidatedOn = CASE WHEN the_table.ID = @ID THEN MAX_DATE() ELSE NOW() END
WHERE surrogate_key = @KEY
AND InvalidatedOn > NOW();
then, even if you intentionally created a race condition, you should always end up with one and only one "definitive" record.