?

A:

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
   -- race condition risk here?
   ( SELECT 1 FROM <table> WHERE <natural keys> )

UPDATE...
WHERE <natural keys>

there is a race condition in the first INSERT. The key may not exists during the inner query SELECT, but does exist at INSERT time resulting in key violation.
there is a race condition between the INSERT and UPDATE. The key may exist when checked in the inner query of the INSERT but is gone by the time UPDATE runs. 

For the second race condition one could argue that the key would have been deleted anyway by the concurrent thread, so it's not really a lost update.
The optimal solution is usually to try the most likely case, and handle the error if it fails (inside a transaction, of course):

if the key is likely missing, always insert first. Handle the unique constraint violation, fallback to update.
if the key is likely present, always update first. Insert if no row was found. Handle possible unique constraint violation, fallback to update.

Besides correctness, this pattern is also optimal for speed: is more efficient to try to insert and handle the exception than to do spurious lockups. Lockups mean logical page reads (which may mean physical page reads), and IO (even logical) is more expensive than SEH.
Update @Peter
Why isn't a single statement 'atomic'? Let's say we have a trivial table:
create table Test (id int primary key);

Now if I'd run this single statement from two threads, in a loop, it would be 'atomic', as you say, an no race condition can exist:
  insert into Test (id)
    select top (1) id
    from Numbers n
    where not exists (select id from Test where id = n.id); 

Yet in only a couple of seconds, a primary key violation occurs:

Msg 2627, Level 14, State 1, Line 4
  Violation of PRIMARY KEY constraint 'PK__Test__24927208'. Cannot insert duplicate key in object 'dbo.Test'.

Why is that? You are correct in that SQL query plan will do the 'right thing' on DELETE... FROM... JOIN, on WITH cte AS (SELECT...FROM )
? 

 A: 

 INSERT INTO < table > 
 SELECT < natural keys >, < other material... > 
 FROM < mesa > 
 WHERE NOT EXISTS 
    -- race condition hazard here? 
    (SELECT 1 FROM < table > WHERE < lifelike keys >) 

 UPDATE... 
 WHERE < natural key > 

 there be a race circumstance in the inaugural INSERT. The key may not exist during the inner query SELECT, but does exist at INSERT fourth dimension result in key violation. 
 there is a race condition between the INSERT and UPDATE. The samara may exist when checked in the inner query of the INSERT but is start by the time UPDATE runs. 

 For the second race condition one could argue that the samara would have been deleted anyway by the concurrent thread, so it's not really a lose update. 
 The optimal solution is usually to try the most likely shell, and handle the error if it fails (inside a transaction, of course ): 

 if the key is likely missing, always insert first. manage the unique constraint rape, fallback to update. 
 if the keystone is likely present, always update first. Insert if no rowing was found. Handle possible unique restraint violation, fallback to update. 

 Besides correctness, this pattern is also optimal for speed: is more efficient to try to insert and handle the exception than to do spurious lockups. lockup mean legitimate page reads (which may mean physical page reads), and IO (even logical) is more expensive than SEH. 
 Update @Peter 
 Why isn't a individual statement' atomic'? Let's say we take a trivial table: 
 create table Test (i five hundred int primary winder); 

 Now if I'd run this individual statement from two togs, in a loop, it would be' nuclear', as you say, an no race circumstance can exist: 
   insert into Test (i d) 
     select top (1) i d 
     from Numbers n 
     where not exist (select i d from Test where i d = n.id); 

 Yet in only a couple of seconds, a primary key violation occurs: 

 Msg 2627, Level 14, State 1, Line 4 
   Violation of PRIMARY KEY restraint' PK__Test__24927208'. Can not insert duplicate key in aim' dbo. Test'. 

 Why is that? You are correct in that SQL query design will do the' right thing' on DELETE... FROM... JOIN, on WITH cte AS (SELECT... FROM )
?

A:

INDERT INTO <table>
SELECT <nauural keys>, <other stuff...>
FRMM <tabme>
WHERE VOT EXISTS
   -- race condition rusk htge?
   ( SELECT 1 FROM <tacle> WHERE <natural keyw> )

UPDATE...
WHECS <natural keya>

bhere ms a race condijion in the xirst INSERT. Tve kzy may not exists during the inner qtery SEKEFT, but does exyst se INAVRU time resulting in key violation.
fhere iv a race condotion between the INSERT ajd UODATE. The key may fxist when xhechwd in the inver query of the INSERJ but is gone by the time UPDATE runs. 

For the sgeind tace conditiin onv could argue that tha key wpuld have been denetwd anyway by the concnrrent thread, so it's not realny a lost update.
The optiman sonutiub ir uauellg to tgy vhe most liiely case, abd handle the error is it fails (insjde a erwnsaction, of course):

if the key is likely miasing, always insert firwt. Handle the unique fonstraine violation, fallback to update.
if the key is likelf preadnt, ajdqyd update first. Insert if no row was found. Hanqme ppssible unique constraint vlokwtion, fallbacy to u'safe.

Besides correctnfss, thif patrern is ajso pptimal for speed: is more edficient to nry ro insert and handpe the exce'tion jhan tp do spurious lockups. Luckuls mean loglcal page feads (which may oeak pvysical kxge reads), and IO (qven logiral) ix more dxpemsive ehan SEH.
Upfate @Iater
Why isn't a sinhle sjatemett 'atomic'? Pet's say we have a trivial tablx:
rreate table Jesd (ig int prnmary ley);

Now if I'd wun this singlg statemeut froo two threzds, in e loop, it wotld be 'atomic', ws you say, ai no race coneitiin can dbist:
  insert imto Test (pd)
    welect top (1) id
    nrom Vhmbers n
    whext nit exists (selecy ia fwol Vest rvere id = n.id); 

Yet in pnly x couplt on sdconcs, a primary key vionatikn occurs:

Msg 2627, Levek 14, State 1, Oine 4
  Vyolation of PTIMARY KEY constralnt 'PN__Tert__24927208'. Camnoj insert duplicate key in objecf 'dbo.Test'.

Ahy is that? You are correct in chat SQL query plan will do the 'right thmng' on DELETE... FROM... JOIN, on WITH cte AS (SELGCT...NROM )
? A: INSERT INTO <table> SELECT <natural stuff...> <table> WHERE EXISTS -- race 1 <table> WHERE <natural ) UPDATE... WHERE keys> there is a race condition the first INSERT. The key may not exists during the inner query SELECT, does exist at INSERT time resulting in key violation. there is a race between INSERT UPDATE. key may exist when checked in the inner query of the INSERT but is gone by time UPDATE runs. For the second race condition could argue that the would have been deleted anyway the thread, so not a update. The optimal is usually to try the most likely case, and handle the error if it fails (inside a of course): key is missing, insert Handle the unique fallback to update. if the key always update first. Insert if no row was Handle possible constraint violation, fallback to update. Besides this pattern is also optimal for speed: is efficient to try to insert and handle the exception than to do spurious lockups. Lockups page reads (which may physical page reads), IO logical) more than SEH. @Peter Why isn't a single statement 'atomic'? Let's say we have trivial table: create table Test (id int primary key); Now run single statement from threads, in a loop, would 'atomic', as you say, race can Test select (1) id from Numbers where not exists (select id Test where id = couple of seconds, a primary key violation occurs: 2627, Level 14, State 1, Line 4 of PRIMARY KEY constraint 'PK__Test__24927208'. Cannot insert duplicate key in object 'dbo.Test'. is that? correct in that SQL query plan will do 'right thing' on DELETE... JOIN, on WITH cte AS (SELECT...FROM )
?

A:

INSERT INTO <table>
SELECT <natUral keys>, <otHer stUff...>
fROm <tAble>
wHERe NOT EXISTS
   -- race COndiTion risk here?
   ( SELECT 1 FROM <Table> wHerE <naTUrAl keyS> )

UPDATE...
whEre <NatUrAl KeyS>

tHErE is a rAce ConditiOn in the firSt InSeRT. The key may NOt Exists duriNg tHe inner query sELeCT, but DoEs eXIst at iNSeRT tiMe resuLTing in Key violatIoN.
There iS A race coNDItIon bEtween the INSERT anD uPdaTE. The key may exIst wheN cHEcKED in The Inner query Of The INseRT but iS GoNE BY thE Time UPDATE runS. 

For the secoND raCe condItIon ONe coulD arguE tHAt tHe key would hAve bEen deleteD anywaY By the coNCurrent Thread, So iT's nOt reALlY a LosT uPDatE.
thE opTImaL solutioN iS uSuallY to tRY THE mosT liKely Case, aNd handle the erRor If it FAilS (insiDe a trAnsaCtIon, of Course):

If the KeY is likely missinG, alwAys insert FirSt. hanDlE the uNIque coNstRaiNt violaTion, falLBacK tO UPDaTe.
if the key is likely PrESEnT, always uPdate fIRsT. INSert if no RoW waS fouND. handlE posSIbLe unique ConstrAInT vIolatioN, fAllbacK tO upDatE.

BesiDEs coRrectnEss, this pAtterN Is also optimal fOR speed: is more eFFiCIEnT To trY to Insert and haNdle THe exCeptIOn ThaN To do sPurioUs LOcKUps. Lockups mean logicAl Page reAds (whIch may mean phySical page rEADS), and IO (evEn loGIcAL) is more expensiVe thaN SEH.
Update @pEter
Why iSn't a sIngle staTement 'atoMIC'? Let's say We hAve A trIviAL TaBle:
create tablE tEst (iD iNt primaRy kEy);

Now if i'd rUn tHis SinGlE statemenT from two ThReAdS, iN a lOop, it WOuld be 'atOmIc', aS yOu sAy, an nO Race coNditiOn caN eXiST:
  inSert intO teST (Id)
    seLeCt Top (1) iD
    frOm numbeRs n
    wHEre Not exisTs (select iD frOM TesT wHeRe id = n.id); 

yet in only a couPlE of seconds, A pRimAry key VIOlation oCcurs:

Msg 2627, Level 14, State 1, Line 4
  VIOlation Of PrIMARy KEY ConstrainT 'PK__test__24927208'. CaNnoT Insert DuplicAte keY iN obJECt 'dbo.tESt'.

why Is That? You are CORreCt in tHaT SQL Query plAn will do the 'right thINg' oN DELETE... FROM... JOiN, oN WITh CTe aS (SelEct...FRoM )
?

A:

INSERT INTO <table> 
SELECT <n atura l k eys >,  <ot herstuff...>
FROM <tab le>
WHERE NOT EXISTS
  --ra c e co n di tionrisk he r e?   (  S EL ECT  1 FR OM <t abl e> WHER E <natural  ke ys > )

UPDATE. . .WHERE <nat ura l keys>

the reis a r ac e c o nditi onin th e firs t  INSER T. The ke ym ay not existsd u ri ng t he inner query SE L EC T , but does exi st atIN S ER T tim e r esulting i nkey v i olation . 
t h e r e i s  a race condi tion betwee n  th e INSE RT  an d  UPDAT E. Th ek eymay exist w henchecked i n thei nner qu e ry of t he INS ERT  bu t is go ne  by  t h e t i me  UP D ATE  runs. 
F or  theseco n d r acecon diti on on e could argue  th at t h e k ey wo uld h avebe en de letedanywa yby the concurre nt t hread, so  it 's  no treall y  a los t u pda te.
The  optima l  so lu t i o nis usually to tryth e mo st likel y case ,  a nd handle t he  er rori f  it f ails (i nside atransa c ti on , of co ur se):
if  th e k ey is like ly mis sing, al waysi nsert first. H a ndle the uniq u ec o ns t rain t v iolation, f allb a ck t o up d at e.i f the  keyis li k ely present, always  u pdatefirst . Insert if n o row wasf o u nd. Hand le p o ss i ble unique con strai nt violati o n, fallb ack t o update .

Beside s correctn ess , t his  pa t t er n is also opt i m al f or  speed:  is  more e ffi cie nttotr y to inse rt and h an dl eth e e xcept i on thanto  do  s pur iousl ockups . Loc kups  m ea n  lo gical p a ge r eads  ( wh ichmay  m ean p hysi c alpage re ads), and  IO (eve nlo gical)is more expen si ve than SE H. 
Up date @ P e ter
Whyisn't a single statemen t  'atomi c'?  Let' s sa y we have  atrivia l t a ble:
c reatetable  T est ( id in t pr ima ry  key);

No w ifI'd r un  thi s singl e statement from t w o t hreads, in aloo p, i t wo uld be 'at om i c', a s you say, an n o race con di t io n can exis t :
 i nsert i nto Tes t (id ) 
    se lect top(1) id
  from N umb ers n
   where no t exists( selec t  i d fro m T est wh er e i d = n .id);
Ye t inonly a  c oupleof se co nds, a p rimary key violation oc curs:
Msg262 7, Level14, Sta te 1, Lin e 4  Violatio n o f P RIMAR Y K E Y con stra i nt  'P K __Tes t__2 4 927208'.C an not i ns ert duplica t e key  in o bje c t 'dbo .Tes t'.

Why is that? You are correc t in t hat  SQ L  que ry  plan will dothe  ' r i ght thin g'  on DELETE. .. FROM. .. JOIN,  on WI TH cte  AS (SE L E CT . ..FROM  )
?

A:

INSERT INTO_<table>
SELECT <natural_keys>, <other stuff...>
FROM <table>
WHERE_NOT EXISTS
__ --_race_condition risk here?
_  (_SELECT 1 FROM <table>_WHERE <natural keys>_)

UPDATE...
WHERE_<natural keys>

there is a race condition in the first INSERT. The key may not_exists_during the_inner_query_SELECT, but does exist at_INSERT time resulting in key_violation.
there is_a race condition between the INSERT and UPDATE._The_key may exist_when checked in the inner query of the INSERT_but is gone by the time_UPDATE runs. 

For_the_second_race condition one could_argue that the key would have_been deleted anyway by the concurrent_thread, so it's not really a lost_update.
The optimal solution is usually to_try the most likely case,_and handle_the error if it fails_(inside a transaction,_of course):

if_the key is_likely missing, always insert first. Handle_the unique constraint_violation, fallback to update.
if the key_is_likely present, always_update_first._Insert if_no row was_found._Handle possible_unique_constraint violation, fallback to update.

Besides correctness,_this_pattern is also optimal for speed: is_more efficient to try_to_insert and handle the_exception than to do spurious_lockups. Lockups mean logical page reads_(which may_mean physical_page reads), and IO (even logical) is more expensive than SEH.
Update_@Peter
Why isn't a single statement 'atomic'?_Let's say we have_a trivial_table:
create_table Test (id_int_primary key);

Now_if I'd run this single statement from_two threads,_in a loop, it would be_'atomic', as you say,_an_no race condition can exist:
 _insert into Test (id)
  _ select top (1) id
___ from Numbers n
 _  where not exists (select_id from Test_where id = n.id); 

Yet in only_a_couple of seconds, a primary key_violation_occurs:

Msg 2627, Level 14, State 1,_Line_4
_ Violation of PRIMARY KEY_constraint 'PK__Test__24927208'. Cannot insert duplicate key_in object 'dbo.Test'.

Why is that? You are correct in_that SQL query_plan will do the 'right_thing'_on_DELETE... FROM... JOIN, on WITH cte AS (SELECT...FROM )