What is the benfit of MERGE in SQL 2008?

Question

question

Kristen asked Oct 7, '09

What is the benfit of MERGE in SQL 2008?

In another question I asked ("What changes have you made to your SQL Code in SQL 2008") I received a couple of responses that MERGE was a particularly useful addition.

I'd be grateful for your thoughts on how it might impact what we currently do (I'm still stuck in the SQL 2000 world)

For our CRUD applications we have a "SAVE" Sproc that is designed to save a new record, or update an existing one. This Sproc is mechanically generated (although the validation it can perform, and sometimes other aspects, is created by humans!)

The premise of our SAVE Sproc is that is has a parameter for every available field. All fields are VARCHAR (or TEXT where necessary). Any parameter with a value (i.e. not NULL) means that the field was presented to the user [on a maintenance form] and thus an Empty String value in that field means the field should be cleared (we actually set the field to NULL). Conversely a parameter which is NULL, the default for all parameters, means that the field was not on the maintenance form, and thus has not been "presented" to the Sproc. As such we take "Blank string" to mean "Empty the field" and NULL to mean "Ignore the field".

This allows us to add columns to the table and any existing forms will not interfere with the new columns - the Form will not present those columns to the SAVE Sproc, and the Sproc will leave them unchanged

Additionally we have an "Edit Number" column in every table (along with Create Date/User and Update Date/User). The Edit Number is stored (in a hidden field) on the maintenance forms, and thus is passed to the SAVE Sproc. If the Edit Number does not match the current value then someone else has saved the record in the meantime, and the later user's save is disallowed - "pessimistic locking" (or is it "Optimistic locking", gosh I'm getting old!

(We have two special values of Edit Number: NULL = Forced Insert - the record must not already exist; -1 = The record MUST exist, but I don't know & don't care what Edit Number it has - useful if just updating the Status, say)

The Sproc contains a conditional IF based on the value of Edit Number. If >= 1 then perform an UPDATE, if NULL perform an insert. (If Edit Number = -1 then get the actual Edit Number, if a record exists, ahead of this test - the use of -1 is intentionally a rare, and carefully considered, event, thus not a performance burden)

All-in-all I consider our SAVE Sprocs to be "Upserts" as they can do either, hence the question about comparison with MERGE. However, our SAVE Sproc does not need to do an EXISTS test (unless given the "-1" Edit Number parameter)

sql-server-2008 merge

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-07T16:22:26Z

mrdenny answered Oct 7, '09

You'd have to have a lot of logic in the WHEN MATCHED THEN UPDATE part of the statement. No less then you have now, probably more. Drop your data into a table variable (or better yet use a table input parameter) then do a merge between the table variable and the table.

WHEN MATCHED AND target.YourEditNumber <= source.YourEditNumber            
   THEN UPDATE SET target.Column1 = CASE WHEN source.Column1 IS NULL THEN             
        target.Column1             
   ELSE            
        CASE WHEN Source.Column1 = '' THEN NULL ELSE Source.Column1 END            
   END            
   ...            
WHEN MATCHED AND source.YourEditNumber IS NULL            
   THEN INSERT            
   ...            
WHEN MATCHED AND source.YourEditNumber = -1            
   THEN UPDATE {SomethingHere}...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-13T11:31:02Z

David 1 answered Oct 13, '09

One big benefit of MERGE is that it's a better alternative to the buggy, non-standard and ill-conceived "UPDATE ... FROM" syntax. MERGE can be used as a safer and better alternative to UPDATE ... FROM even when you only want to do UPDATEs. And it's generally quicker than the UPDATE!

Hopefully UPDATE ... FROM can now be deprecated and removed in some future version.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question