Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?

Question

I'm unable to test it and get the solution by myself right now, and i haven't found the information on MSDN nor at google.

I was questioned if a merge statement that inserts, deletes and updates records in a given table need to be encapsulated in a transaction (if a failure occurs after the inserts were done, during the updates for example), or if any of the operations fails the whole merge fails as well.

Maybe it would not harm if we included an transaction, but for the sake of curiosity, we wish to know more about the merge internals.

Answer 1

Any statement in SQL Server is a transaction in it's own right.

That is, it is atomic: everything succeeds or everything fails

An explicit transaction would be used to group multiple single atomic statements into one big atomic transaction.

This is beauty of MERGE: no need for an explicit transaction and 3 separate statements.

Answer 2

All DML statements in SQL Server are ran in an implicit transaction if an explicit is not started. Of course you can still wrap it in your own explicit transaction but shouldn't be needed

Answer 3

Every query runs in a transaction, always. If you don't create a transaction, the query itself has it's own transaction. If anything goes wrong, the entire query is rolled back.

If you want to do some more error handling you may want a transaction around it, but it's not necessary for the consistency of the database.

Answer 4

Like others have said, you don't need a transaction, but you should think about your transaction isolation level or hints if you're doing something like upsert. Using merge for upserts can cause conflicts in the default configuration.