P support or not.

With low overhead, and higher speed, I'd bet for reliable UDP, than the bulky TCP - for developing such applications(gaming).

 Q:

Atomic UPSERT in SQL Server 2005

What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists, INSERT otherwise) in SQL Server 2005?
I see a lot of code on SO (e.g. see Check if a row exists, otherwise insert) with the following two-part pattern:
UPDATE...
FROM...
WHERE <condition>
-- race condition risk here
IF @@ROWCOUNT = 0
  INSERT...

or
IF (SELECT COUNT(*) FROM... WHERE <condition>) = 0
  -- race condition risk here
  INSERT...
ELSE
  UPDATE...

where < condition > will be an evaluation of natural keys.  None of the above approaches seem to deal well with concurrency.  If I cannot have two rows with the same natural key, it seems like all of the above risk inserting rows with the same natural keys in race condition scenarios.
I have been using the following approach but I'm surprised not to see it anywhere in people's responses so I'm wondering what is wrong with it:
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>

Note that the race condition mentioned here is a different one from the ones in the earlier code.  In the earlier code, the issue was phantom reads (rows being inserted between the UPDATE/IF or between the SELECT/INSERT by another session).  In the above code, the race condition has to do with DELETEs.  Is it possible for a matching row to be deleted by another session AFTER the (WHERE NOT EXISTS) executes but before the INSERT executes?  It's not clear where the WHERE NOT EXISTS puts a lock on anything in conjunction with the UPDATE.
Is this atomic?  I can't locate where this would be documented in SQL Server documentation.
EDIT:  I realise this could be done with transactions, but I think I would need to set the transaction level to SERIALIZABLE to avoid the phantom read problem?  Surely that is overkill for such a common problem
P support or not. 

 With low overhead, and eminent focal ratio, I'd bet for reliable UDP, than the bulky TCP - for developing such applications(gaming). 

  Q: 

 nuclear UPSERT in SQL Server 2005 

 What is the correct pattern for doing an nuclear " UPSERT " (UPDATE where exist, INSERT otherwise) in SQL Server 2005? 
 I attend a lot of code on SO (e.g. experience Check if a row exist, differently insert) with the following two - part design: 
 UPDATE... 
 FROM... 
 WHERE < condition > 
 -- race circumstance risk here 
 IF @@ROWCOUNT = 0 
   INSERT... 

 or 
 IF (SELECT COUNT (*) FROM... WHERE < condition >) = 0 
   -- race circumstance risk here 
   INSERT... 
 ELSE 
   UPDATE... 

 where < condition > will be an evaluation of natural keys.   None of the above approaches look to deal well with concurrency.   If I cannot have two rows with the same lifelike key, it seems like all of the above risk inserting rows with the same lifelike key in race condition scenarios. 
 I have been using the following approach but I'm surprised not to see it anywhere in people's responses so I'm wonder what is wrong with it: 
 INSERT INTO < table > 
 SELECT < lifelike key >, < early stuff... > 
 FROM < table > 
 WHERE NOT EXISTS 
    -- race condition risk here? 
    (SELECT 1 FROM < table > WHERE < natural keys >) 

 UPDATE... 
 WHERE < lifelike keys > 

 Note that the race condition mentioned here is a different one from the ones in the earlier code.   In the earlier code, the issue was phantom reads (rows being tuck between the UPDATE / IF or between the SELECT / INSERT by another session).   In the above code, the subspecies condition has to do with DELETEs.   Is it possible for a matching rowing to be deleted by another session AFTER the (WHERE NOT EXISTS) carry through but before the INSERT executes?   It's not clear where the WHERE NOT EXISTS puts a lock on anything in conjunction with the UPDATE. 
 Is this atomic?   I can't settle where this would be documented in SQL Server software documentation. 
 EDIT:   I realise this could be done with transactions, but I think I would need to sic the transaction level to SERIALIZABLE to debar the phantom read trouble?   Surely that is overkill for such a common problem
P skpport or not.

With low ovtrhead, and higher speed, M'd bet ror relixble UDP, than the bulky TCP - fir deceloping such applicatkons(gaminh).

 Q:

Atomix UPWWRT in SQL Server 2005

What ia the rorrect pattern for doing an atomic "UPSARG" (BPDATE where exists, INSERT otherwise) in SQL Sfrver 2005?
I see a jot ps cosv in SO (e.g. see Check if a row esists, ouherwise insert) wiyh the following two-part pwttegn:
UPDATE...
FROM...
WHERE <fondition>
-- rqce sindition risy here
IF @@ROWCOUNT = 0
  IHSERT...

or
IF (SELECT COUNT(*) FROM... WHEFE <coudition>) = 0
  -- rqce wondition rmsk hege
  INSERT...
ELSC
  UPDADE...

where < condition > wlll bx an evaluation of naturan keys.  None of thg above ap[rkaches seem to deql well witv covxurfendy.  Jf I cwnnkt have twk rows with the same natural kty, yn seems like zll of tre above risk inserting rows with the sdme natural keys in race cindition scenarios.
I hwve been tsing the following approach but I'm surprised not to sxe it akywhdee in people's responses so I'm wondering what is srpnn with it:
INSERT LNTO <table>
SELECT <nstkrsj keys>, <other rtuff...>
FXKM <table>
WHERE NOT EXLSTS
   -- race conditiog rixk here?
   ( SELECT 1 FROM <taboe> WHERE <natlral keys> )

UPDATE...
WHERE <uatural keys>

Uote tnat tne race condition mentiuned here is a fifferent une from the oner ik tve earlitf code.  In the eawlier codx, the issue das khantom reads (rowd beikc inserted between the OPDATE/HF or betwfen the SELECT/INSERT by another session).  In tne abmve code, the vace condition ras to do with DELETEf.  Is it possibme for e matching rjw to be deledgd by another session AFTWR tye (WHERD NOT EXISTS) exrcutes bun yefore thw INSERT executes?  It'r not clear wherz uhw WHERE NOT EXOSTR pttd e loch on anything in zonginctiun with the JPDAYE.
Is this atomic?  I wan't locate where this wjuld be eocumentqd in SQL Serfer documentation.
EFIT:  M realmse thos sould be done with transactiona, but I tjinh I would neqd ti set the trcnsaction level to SERIALIZABLE to avoid the phantom read problem?  Surely thaj ix overkill hor sush a commmn problem
P support or not. With low overhead, speed, bet for UDP, than the such Q: Atomic UPSERT SQL Server 2005 is the correct pattern for doing atomic "UPSERT" (UPDATE where exists, INSERT otherwise) in SQL Server 2005? I see lot of code on SO (e.g. see Check if a row exists, otherwise with following pattern: FROM... WHERE <condition> -- race condition risk here IF @@ROWCOUNT = 0 INSERT... or IF (SELECT FROM... WHERE <condition>) = 0 -- race condition here INSERT... ELSE UPDATE... < condition > will be evaluation natural keys. of above seem to deal with concurrency. If I cannot have two rows with the same natural key, it seems like all the above rows with same keys race condition scenarios. been using the following approach but to see it anywhere in people's responses so wondering what wrong with it: INSERT INTO <table> <natural keys>, <other stuff...> FROM <table> WHERE NOT -- race condition risk here? ( SELECT 1 FROM <table> WHERE <natural keys> ) UPDATE... keys> Note that the condition mentioned here a one the in the code. In the earlier code, the issue was phantom reads (rows inserted between the UPDATE/IF or between the SELECT/INSERT by another the code, the race has to do with Is possible for a matching be by the NOT executes but before the executes? It's not clear where WHERE NOT EXISTS puts conjunction with the UPDATE. Is this atomic? I locate where this would be documented in Server documentation. EDIT: I realise this could be done with transactions, but think I to set the transaction level to SERIALIZABLE to the phantom read problem? that is overkill for such a common problem
P support or not.

With low overhEad, and highEr speEd, I'D beT fOr reLiabLe UDP, than the buLKy TCp - for developing such applIcatiOnS(GamiNG).

 Q:

atomiC UPSERT IN Sql serVeR 2005

WHat Is THe CorreCt pAttern fOr doing an aTomIc "uPSERT" (UPDATE WHeRe exists, INsERt otherwise) in sQL server 2005?
i sEe a LOt of cOde On SO (e.G. see ChECk if a rOw exists, oThERwise iNSert) witH THe FollOwing two-part patteRN:
UpdATE...
FROM...
WHERE <cOnditiOn>
-- RAcE COndItiOn risk here
iF @@rOWCOunT = 0
  INSERt...

Or
if (seLEct COUNT(*) FROM... WHErE <condition>) = 0
  -- RAce ConditIoN riSK here
  InSERT...
eLse
  UPdATE...

where < coNditIon > will be An evalUAtion of NAtural kEys.  NonE of The AbovE ApPrOacHeS SeeM To DeaL WelL with conCuRrEncy.  IF I caNNOT Have Two Rows With tHe same natural Key, It seEMs lIke alL of thE aboVe Risk iNsertiNg rowS wIth the same naturAl keYs in race cOndItIon ScEnariOS.
I have BeeN usIng the fOllowinG AppRoACH BuT I'm surprised not to sEe IT AnYwhere in People'S ReSpONses so I'm WoNdeRing WHAt is wRong WItH it:
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>

notE That tHe racE cONdITion mentioned here is A dIffereNt one From the ones in The earlier CODE.  In the eaRlieR CoDE, the issue was phAntom Reads (rows bEIng inserTed beTween the uPDATE/IF oR BEtween thE SElECt/INsERt BY aNother session).  iN The aBoVe code, tHe rAce condItiOn hAs tO do WiTh DELETEs.  is it possIbLe FoR a MatChing ROw to be deLeTed By AnoTher sESsion AfTER tHe (WHeRe Not EXiSTS) exeCUtES But bEfOrE the iNSeRt execUtes?  iT's nOt clear Where the WhERe nOT ExIsTs puts a lOck on anything In ConjunctioN wIth The UPDate.
Is this aTomic?  I can't locate where thIS would bE doCumenTed iN SQL ServeR doCumentAtiON.
EDIT:  I RealisE this CoUld BE Done wITH tRanSaCtions, but I THInk i woulD nEed tO set the Transaction level to seRIaLIZABLE to avoId tHe phANToM reAD pROblEm?  sUreLY That is overkill fOr such a comMoN PrOblem
P support or not.

With lo w overhead , and  hi ghe rspee d, I 'd bet for rel i able  UDP, than the bulky T CP -fo r  dev e lo pingsuch ap p li c a tio ns (g ami ng ) .
 Q:
At omic UP SERT in SQ L S er ver 2005

Wh a tis the cor rec t pattern fo r d oing a nato m ic "U PSE RT" ( UPDATE whereexists, I NS E RT oth e rwise)i n  S QL S erver 2005?
I see al ot of code onSO (e. g. se e Che ckif a row e xi sts,o therwis e  i n s e rt) with the foll owing two-p a rtpatter n: 
UP D ATE... 
FROM .. . 
WH ERE <condit ion> 
-- racecondit i on risk here
IF  @@ROW COU NT= 0 I NS ERT .. . 

o r 
I F ( S ELE CT COUNT (* )FROM. .. W H E R E  <co ndi tion >) =0
  -- race c ond itio n  ri sk he re
 INSE RT ...
E LSE
 UPDAT E. ..

where < con diti on > will  be  a n e va luati o n of n atu ral  keys. None o f  th ea b o ve  approaches seem t od e al  well wi th con c ur re n cy.  IfIcan noth a ve tw o ro w swith the  samen at ur al key,  i t seem slik e a ll of theaboverisk ins ertin g  rows with the same naturalk ey s in race  co ndition sce nari o s.
I  hav e  b een using  thefo l lo w ing approach but I' msurpri sed n ot to see itanywhere i n p eople'sresp o ns e s so I'm wonde ringwhat is wr o ng withit:
I NSERT IN TO <table > SELECT < nat ura l k eys > ,  < other stuff.. . > 
FRO M<table> 
WH ERE NOT  EX IST S
  - -race cond ition ri sk  h er e? 
  ( SE L ECT 1 FR OM  <t ab le>  WHER E  <natu ral k eys>  ) 
U PDA TE...
W H ER E <nat ur al  key s>
N ote t hatt herace co ndition m ent i oned  h er e is adifferent one  f rom the on es  in  the e a r lier cod e.  In the earlier code ,  the is sue  wasphan tom reads  (r ows be ing insert ed bet weenth e U P D ATE/I F or  be tw een the SE L E CT/ INSER Tby a nothersession).  In thea bov e code, the r ace  con d i ti onh as todo wit h DELETEs.  Is it  possiblefo r  a  matchingr owto  be del eted by  anot h er sess ion AFTER  the (WHE RE  NOT E XIS TS) execut es but b efore the INSER T  e xecut es?   It's  n otclear  where the  WHER E NOTEX ISTS p uts a  l ock on a nything in conjunctionwith t he UP DAT E.
Is thi s a t omi c?  I can 't l ocate wher e t his  woul d b e  docu ment e dinS QL Se rver documenta t io n.E D IT :  I realis e t his  coul d b e  donewith  transactions, bu t  I think I wou ld n e e d t o s e t th etransaction le vel  t o SERIALIZ AB LE to avoid  the pha nt o m rea d prob lem? Surelyt h at is ove rkil l f or such a  co mm o n probl em 
P support_or not.

With_low overhead, and higher_speed, I'd_bet_for reliable_UDP,_than the bulky_TCP - for_developing such applications(gaming).

 Q:

Atomic_UPSERT in SQL_Server_2005

What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists, INSERT_otherwise)_in SQL_Server_2005?
I_see a lot of code_on SO (e.g. see Check_if a_row exists, otherwise insert) with the following two-part_pattern:
UPDATE...
FROM...
WHERE_<condition>
-- race condition_risk here
IF @@ROWCOUNT = 0
  INSERT...

or
IF (SELECT COUNT(*)_FROM... WHERE <condition>) = 0
 _-- race condition_risk_here
_ INSERT...
ELSE
  UPDATE...

where_< condition > will be an_evaluation of natural keys.  None_of the above approaches seem to deal_well with concurrency.  If I_cannot have two rows with_the same_natural key, it seems like_all of the_above risk_inserting rows with_the same natural keys in race_condition scenarios.
I have_been using the following approach but_I'm_surprised not to_see_it_anywhere in_people's responses so_I'm_wondering what_is_wrong with it:
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>

Note that the_race condition_mentioned here_is a different one from the ones in the earlier code._ In the earlier code, the_issue was phantom reads_(rows being_inserted_between the UPDATE/IF_or_between the_SELECT/INSERT by another session).  In the_above code,_the race condition has to do_with DELETEs.  Is_it_possible for a matching row to_be deleted by another session AFTER_the (WHERE NOT EXISTS) executes_but_before_the INSERT executes?  It's_not clear where the WHERE NOT_EXISTS puts a_lock on anything in conjunction with the_UPDATE.
Is_this atomic?  I can't locate_where_this would be documented in SQL_Server_documentation.
EDIT:_ I realise this could_be done with transactions, but I_think I would need to set the transaction level_to SERIALIZABLE to_avoid the phantom read problem?__Surely_that is overkill for such a common problem