Most applications are written nowadays using optimistic concurrency. Locks aren’t placed on a piece of data for the duration of someone viewing that data in their application. Instead what you do is when someone wants to save the data you check to see whether someone else as changed it since the time you read the data. If someone has you generally don’t want to save the data because you are likely to overwrite the change that had been made. The check normally consists of checking the value of each column against the values you got back when you read the data or comparing the value of a rowversion against what you read (a rowversion column gets changed every time a row is changed, that means you can use this column to detect a change rather than checking all the columns). Imagine buying a

What has this got to do with the MERGE statement. Well optimistic concurrency is very easy to do when you are dealing with a single row of data as you only have to do one check. If you identify the row has changed you can return success or failure. However with a MERGE statement what happens if you are updating 10 records and 4 have already been updated. In this case you have a number of options one is to update those that haven’t changed and return to the client those that have changed. This can be done very nicely using a trick with MERGE and the OUTPUT clause .

Assuming you have a rowversion (timestamp) column on your table (you can use the same approach if you don’t, in which case you will have to compare all the columns ) the following is an example MERGE statement with a TVP (Table Valued Parameter). We are going to save a set of OrderLines

The classic MERGE statement is as follows

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 

              ,.....

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);

To add optimistic checking you can check at the start within a serialised transaction or check at the end to see if the number of rows affected is the same as the number passed in i.e.

SET TRANSACTION ISOLATION SERIALIZABLE

BEGIN TRANSACTION

IF NOT EXISTS (SELECT 1

             FROM Sales.SalesOrderDetail OrderDetail

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

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

            WHERE OrderDetail.Rowversion <> Chng.Rowversion)

    BEGIN

        MERGE Sales.SalesOrderDetail OrderDetail

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

        .......

    END      

ELSE

    RAISERROR(‘data has changed since it was read’....)

COMMIT TRANSACTION

Or

BEGIN TRANSACTION

    MERGE Sales.SalesOrderDetail OrderDetail

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

    .......

IF @@ROWCOUNT<> (SELECT Count(1) FROM @ChangedOrderDetail)

       BEGIN

    RAISERROR(‘data has changed since it was read’....)

       ROLLBACK TRANSACTION

       END

ELSE

    COMMIT TRANSACTION

The final way is to do a partial update and report the records that have changed . The OUTPUT clause only contains records that have been inserted, updated or deleted. So to get the records out you need to UPDATE them, however you can UPDATE the columns to their existing values (i.e. not change anything ) and that way you can get these rows in the OUTPUT clause. You can then use a nice feature of the MERGE OUTPUT clause and compare the deleted rowversion value with that in the source data. If they are different it means the record wasn’t changed.

MERGE Sales.SalesOrderDetail OrderDetail

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

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

WHEN MATCHED THEN

    UPDATE SET [SalesOrderID]            = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                THEN Chng.SalesOrderID           

                                                ELSE OrderDetail.SalesOrderID

                                           END

              ,[CarrierTrackingNumber]   = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                THEN Chng.CarrierTrackingNumber 

                                                ELSE OrderDetail.CarrierTrackingNumber

                                           END

              , .....

OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 END Changed

      , Chng.SalesOrderDetailID

Note: For simplicity I have removed most of the columns from the update statement. You would need to include these in your application.

You can further extend this by using another new feature of SQL Server 2008, Composable DML. This allows you to consume the output from one statement into another statement. With SQL Server 2008 you are limited to only being able to consume the data in an INSERT statement. There are also restrictions on the structure of the table you are inserting into. Refer to BOL for exact details.

Using composable DML you can store only those records that failed update into a table variable and then return those records.

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

DECLARE @t TABLE (Changed int, ID int)

INSERT INTO @t

SELECT Changed, SalesOrderDetailID

FROM (MERGE Sales.SalesOrderDetail OrderDetail

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

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

       WHEN MATCHED THEN

        UPDATE SET [SalesOrderID]            = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                    THEN Chng.SalesOrderID           

                                                    ELSE OrderDetail.SalesOrderID

                                               END

                  ,[CarrierTrackingNumber]   = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                    THEN Chng.CarrierTrackingNumber 

                                                    ELSE OrderDetail.CarrierTrackingNumber

                                               END

       OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 END Changed

            , Chng.SalesOrderDetailID) da

WHERE CHanged = 0

SELECT *

  FROM @t