What's the difference between these three versions of TSQL snippet?

Question

Version 1

DECLARE @key INTEGER = 33, @val INTEGER = 44;
BEGIN TRANSACTION;
INSERT dbo.t([key], val)
  SELECT @key, @val
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE)
      WHERE [key] = @key
  );
IF @@ROWCOUNT = 0
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
COMMIT TRANSACTION;

Version 2

DECLARE @key INTEGER = 33, @val INTEGER = 44;
BEGIN TRANSACTION;
INSERT dbo.t WITH (UPDLOCK, SERIALIZABLE) ([key], val)
  SELECT @key, @val
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.t
      WHERE [key] = @key
  );
IF @@ROWCOUNT = 0
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
COMMIT TRANSACTION;

Version 3

DECLARE @key INTEGER = 33, @val INTEGER = 44;
BEGIN TRANSACTION;
INSERT dbo.t WITH (UPDLOCK, SERIALIZABLE) ([key], val)
  SELECT @key, @val
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE)
      WHERE [key] = @key
  );
IF @@ROWCOUNT = 0
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
COMMIT TRANSACTION;

I'm confused about the position for the hint. If the table in the INSERT portion and the subquery is the same, does it still make a difference where to write the hint?

Answer 1

The hints only apply where they are placed. It doesn't matter that the source and target tables are the same.

The hints are needed for the NOT EXISTS to ensure (a) the row remains locked for long enough; and (b) if a row doesn't exist in the tested range, that continues to be the case for the duration of the transaction. Hinting the read inside the existence test is the most reliable way to achieve those aims.

Version 1 is one of the correct "upsert" patterns, used where inserts are expected to be more common.

Version 2 lacks the necessary hints on the table read to work correctly under concurrency while minimising deadlocks. There is a small window of opportunity for another session to insert a row in the NOT EXISTS range before the insert happens.

Version 3 unnecessarily duplicates the hints on the target of the insert, but it's otherwise harmless.

See SQL Server UPSERT Patterns and Antipatterns by Michael Swart.