Please stop using UPSERT anti-pattern (SQL Server)

For future students on the course "MS SQL Server Developer" prepared a translation of the article.



We also invite you to watch the open webinar on "Graph Databases in SQL Server" . In this lesson, the participants, together with an expert, will look at what graph databases are and what are the options for working with graphs and hierarchies in SQL Server.

---

I think everyone already knows my opinion of MERGE and why I stay away from it. But here is another anti-pattern that I constantly come across when it is required to execute UPSERT (UPdate inSERT - update a row if it exists, and insert if it does not exist):

IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key)
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
ELSE
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val); 
END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

It looks pretty logical and fits with the way we think about it:

• Is there a string for the given key?

  
  
  
  
  

  • YES : We update this line.

    
    
    
    
    

  • : .

    
    
    
    
    

, , , — . , (, , ). -, , :

, , , ( ):

• UPDATE , ( "", "" " "). , . (Paul White) , (Martin Smith) .

  
  
  
  
  

• , INSERT :

  
  
  
  
  

  • (deadlock) - ;

    
    
    
    
    

  • (key violation), ;

    
    
    
    
    

  • , .

    
    
    
    
    

— , . , XACT_ABORT , — , . , IF EXISTS ( ), . , , .

« ...»

(Dan Guzman) Conditional INSERT/UPDATE Race Condition, "UPSERT" Race Condition With MERGE.

(Michael Swart) Mythbusting: Concurrent Update/Insert Solutions, , , . MERGE Be Careful with the Merge Statement. .

, ( , ):

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

? UPDLOCK ?

• UPDLOCK ( , ). 

  
  
  
  
  

• SERIALIZABLE ( , ). 

  
  
  
  
  

, 1000% . ( ) . , , , . , :

:

• , , . 

  
  
  
  
  

• , «» . ( ), UPDATE. 

  
  
  
  
  

, , - , «» .

, . , . , , .

, UPDATE ?

, UPDATE . , , INSERT, INSERT , UPDATE, UPSERT:

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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

« », INSERT :

BEGIN TRANSACTION;
 
BEGIN TRY
  INSERT dbo.t([key], val) VALUES(@key, @val);
END TRY
BEGIN CATCH
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END CATCH
 
COMMIT TRANSACTION;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. / . .

?

INSERT / UPDATE, (Justin Pealing) , , , ?

- (TVP, Table-Valued Parameters), UPDATE JOIN, INSERT, NOT EXISTS. , :

CREATE PROCEDURE dbo.UpsertTheThings
    @tvp dbo.TableType READONLY
AS
BEGIN
  SET NOCOUNT ON;
 
  BEGIN TRANSACTION;
 
  UPDATE t WITH (UPDLOCK, SERIALIZABLE) 
    SET val = tvp.val
  FROM dbo.t AS t
  INNER JOIN @tvp AS tvp
    ON t.[key] = tvp.[key];
 
  INSERT dbo.t([key], val)
    SELECT [key], val FROM @tvp AS tvp
    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);
 
  COMMIT TRANSACTION;
END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- , TVP (XML, - ..), JOIN . INSERT — UPDATE .

UPSERT- , , , , . , IF EXIST. (Paul White, sql.kiwi | @SQK_Kiwi) — .

MERGE (, - MERGE-), .

---

"MS SQL Server Developer".

« SQL Server».