As we all know, merge statements are ideal for executing multiple actions in a single statement—an insert, update, and delete with one statement and one source query.
This blog is intended to help you make a well-informed decision about when to use a merge statement and when it’s better to avoid it. There is no right or wrong choice, but be aware of the consequences of using a merge statement and the consequences of using separate statements.
When using a merge statement, you must take into account that the source and target tables can be locked. These locks are larger than with separate statements due to lock escalation, which increases the likelihood of deadlocks. You can (partially) prevent this by using a CTE (Common Table Expression) to make the target table smaller, thereby reducing the size of the locks, and by placing the source table in a temporary table so that it doesn't cause a lock during the merge.
For example:
-- Collect data to merge
select j.product_id
, j.product_name
into #imported_products
from openjson(@json)
with (
product_id int '$.ProductID'
, product_name varchar(100) '$.ProductName'
) j
-- Merge #imported_products into product
;with products as
(
select *
from product p
where p.status = 1 --concept /* Make the dataset smaller where possible. */
)
merge products tgt
using #imported_products src
on src.product_id = tgt.product_id
when not matched then insert .... ....Best Practices:
- Do not use merge during production hours on tables that are heavily written to (sales/production/order tables).
- Do use merge outside of business hours, for example, for data migration between systems.
- Do use merge on tables that are not heavily written to.
- For separate insert/update/delete statements, consciously choose an order:
- First, perform a delete to make the set as small as possible.
- Then, perform the update, as the set is now as small as possible.
- Finally, perform the insert.
For further explanation, you can visit Microsoft's page: Concurrency Considerations for Merge