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.
-