Using TABLOCK with merge SQL Server

Question

I have a merge query similar to the one below:

MERGE [Unshipped] u
USING [Order] or ON or.ID = u.ID

WHEN MATCHED AND u.Date <> or.Date
   THEN
      UPDATE 
      SET u.Date = or.Date

WHEN NOT MATCHED
    THEN 
       INSERT (ID, FK, Date)
       VALUES (or.ID, or.FK, or.Date)

If i am to add TABLOCK where is the best place for it? I would have assumed i would put it like below - wrap the whole merge statement:

     MERGE [Unshipped] u WITH (TABLOCK)
     USING [Order] order or ON or.ID = u.ID

or is it more suitable for it to go with the insert statement?

Answer 1

You only need:

 MERGE [Unshipped] WITH (TABLOCK) AS u 
 USING [Order] or ON or.ID = u.ID

Rest of the MERGE statement is unchanged.

Answer 2

The 1st question that you should ask yourself is why do you need this table hint at all? Merge is a DML statement with runs in an implicit transaction. In that way, you'll block the entire table, but if you'd like to do you should put your hint on the table that you NEED to lock. Just have a look at the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

MERGE [Unshipped] u WITH (TABLOCK)

USING [Order] order as or WITH (TABLOCK) ON or.ID = u.ID

It really depends which table you want to lock Unshipped or Order.