SQL Server 2008 - Handling deletes with MERGE

The MERGE statement is a new statement introduced in SQL Server 2008 that allows you to perform inserts, updates and deletes in one statement. You use a rowset as the source for the MERGE and as with all rowsets this can have any number of rows in it. For an introduction see “MERGE an introduction”.

The challenge with deletes is that on the client the row has been deleted, how do you handle the fact you no longer have the row when you use MERGE.

Assuming you are passing a set of data to your MERGE statement there are two way of handling deletes.

The first is the classic example that you will see in most examples. In this solution you work on the principle that if the row doesn’t exist in the source rowset then it must have been deleted and so you perform a DELETE.

This is done by using the WHEN TARGET NOT MATCHED clause of the MERGE statement

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

 

MERGE Sales.SalesOrderDetail OrderDetail

USING @ChangedOrderDetail    Chng        ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

                                        AND Chng.[SalesOrderID]       = OrderDetail.[SalesOrderID]

WHEN MATCHED THEN

    UPDATE SET [SalesOrderID]            = Chng.SalesOrderID           

              ,[CarrierTrackingNumber]   = Chng.CarrierTrackingNumber 

              ,[OrderQty]                = Chng.OrderQty               

              ,[ProductID]               = Chng.ProductID               

              ,[SpecialOfferID]          = Chng.SpecialOfferID           

              ,[UnitPrice]               = Chng.UnitPrice               

              ,[UnitPriceDiscount]       = Chng.UnitPriceDiscount       

              ,[rowguid]                 = Chng.rowguid               

              ,[ModifiedDate]            = Chng.ModifiedDate           

WHEN NOT MATCHED THEN

   INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice]

          ,[UnitPriceDiscount],[rowguid],[ModifiedDate])

   VALUES (Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID

          ,Chng.SpecialOfferID ,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);

WHEN NOT MATCHED IN SOURCE

   DELETE

What this does is perform a scan of the target table  (OrderLines) looking for rows in the source data (@ChangedOrderLines). You will notice I said scan and thats what it has to do, it has to check every row in the target table against the source data. This is a very expensive operation as most scans are. Furthermore it only works if the source data should represent ALL the rows in the target table.

Imagine you have an OrderLines table with 1 million rows. You are not going to retrieve all 1 million rows from your client application to update and then send back to the database. Instead you will return all the OrderLines for a specific Order.

If you use the DELETE pattern above, what will happen is that, your source data will only have data for one Order and won’t have any data for ALL the other Orders in the system. This will mean that ALL the OrderLines for the other orders will NOT MATCH IN TARGET and so be deleted.

This is obviously not ideal.

So whats the solution?

The solution is to use soft deletes. This means that you don’t DELETE the record but instead flag the record as deleted. We can then use the additional feature of MERGE which allows a condition to be added to the WHEN clause (a SQL Server specific extension not in the standard).

This turns our MERGE statement into the following.

 

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

 

MERGE Sales.SalesOrderDetail OrderDetail

USING @ChangedOrderDetail    Chng        ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

                                        AND Chng.[SalesOrderID]       = OrderDetail.[SalesOrderID]

--Note DELETEs are now done here

WHEN MATCHED AND Chng.Action = 'D' THEN

    DELETE

----

WHEN MATCHED THEN

    UPDATE SET [SalesOrderID]            = Chng.SalesOrderID           

              ,[CarrierTrackingNumber]   = Chng.CarrierTrackingNumber 

              ,[OrderQty]                = Chng.OrderQty               

              ,[ProductID]               = Chng.ProductID               

              ,[SpecialOfferID]          = Chng.SpecialOfferID           

              ,[UnitPrice]               = Chng.UnitPrice               

              ,[UnitPriceDiscount]       = Chng.UnitPriceDiscount       

              ,[rowguid]                 = Chng.rowguid               

              ,[ModifiedDate]            = Chng.ModifiedDate           

WHEN NOT MATCHED THEN

   INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice]

          ,[UnitPriceDiscount],[rowguid],[ModifiedDate])

   VALUES (Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID

          ,Chng.SpecialOfferID ,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);

 

This results in a much better query plan as the query engine only as to find those OrderLines that match those in the @ChangedOrderLines table parameter.

Using this pattern does require you to do some additional work on your client, its not difficult and I will show you how you can do that in the next article.

NOTE: This examples use the new syntax for MERGE introduced in RC0 of SQL SERVER 2008.

 



-
Published Wednesday, June 18, 2008 6:18 PM by simonsabin

Comments

# Dew Droplet - June 19, 2008 | Alvin Ashcraft's Morning Dew

Pingback from  Dew Droplet - June 19, 2008 | Alvin Ashcraft's Morning Dew