Best practice for writing with primary key constraint?

Solution 1:

My preferred method for single-row upsert is:

BEGIN TRANSACTION;

UPDATE dbo.t WITH (HOLDLOCK, SERIALIZABLE)
  SET ...
  WHERE [key] = @key;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t ...
END

COMMIT TRANSACTION;

If you believe you will much more often be performing an insert, you can swap the logic around so you try that first:

BEGIN TRANSACTION;
 
INSERT dbo.t ...
  SELECT @key, ...
  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;

Some background:

• Please stop using this UPSERT anti-pattern
• Checking for potential constraint violations before entering TRY/CATCH
• So, you want to use MERGE, eh?