Only inserting a row if it's not already there

What about the "JFDI" pattern?

BEGIN TRY   INSERT etcEND TRYBEGIN CATCH    IF ERROR_NUMBER() <> 2627      RAISERROR etcEND CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes.What if the UPDLOCK is escalated and the whole table is locked?

Read lesson 4:

Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…) line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.

I added HOLDLOCK which wasn't present originally. Please disregard the version without this hint.

As far as I'm concerned, this should be enough:

INSERT INTO TheTable SELECT     @primaryKey,     @value1,     @value2 WHERE     NOT EXISTS     (SELECT 0     FROM TheTable WITH (UPDLOCK, HOLDLOCK)     WHERE PrimaryKey = @primaryKey) 

Also, if you actually want to update a row if it exists and insert if it doesn't, you might find this question useful.

You could use MERGE:

MERGE INTO TargetUSING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2)ON Target.key = Source.keyWHEN MATCHED THEN    UPDATE SET value1 = Source.value1, value2 = Source.value2WHEN NOT MATCHED BY TARGET THEN    INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2)