Does MERGE use tempdb?

Question

Consider the following query:

MERGE [Parameter] with (rowlock) AS target
    USING (SELECT @AreaId, @ParameterTypeId, @Value)
            AS source (AreaId, ParameterTypeId, Value)
    ON (target.AreaId = source.AreaId AND 
        target.ParameterTypeId = source.ParameterTypeId)
    WHEN MATCHED THEN 
        UPDATE SET target.Value = source.Value, @UpdatedId = target.Id
    WHEN NOT MATCHED THEN
        INSERT ([AreaId], [ParameterTypeId], [Value])
        VALUES (source.AreaId, source.ParameterTypeId, source.Value);

Statistics I/O gives the following output:

Table 'ParameterType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Area'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parameter'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Worktable appears in the messages tab which makes me think that tempdb is being used by MERGE.

I am not seeing anything in the Execution plan that would indicate a need for tempdb

Does MERGE always use tempdb?

Is there anything in BOL that explains this behavior?

Would using INSERT & UPDATE be faster in this situation ?

Left

Right

Here is the table structure

Answer 1

^{(Expanding on my comment on the question.)}

Without a unique constraint on the combination of AreaId and ParameterTypeId, the given code is broken because @UpdatedId = target.Id will only ever record a single row Id.

Unless you tell it so, SQL Server can't implicitly know the possible states of the data. Either the constraint should be enforced, or if multiple rows are valid, the code will need to be changed to use a different mechanism to output the Id values.

Because of the possibility that the scan operator will come across multiple matching rows, the query must eager spool all the matches for Halloween protection. As indicated in the comments, the constraint is valid, so adding it will not only change the plan from a scan to a seek, but also eliminate the need for the table spool, as SQL Server will know there is going to be either 0 or 1 rows returned from the seek operator.

Answer 2

If the update can change the position of the row in the index scanned by the update, SQL Server need to protect from the Halloween Problem. For that SQL Server usually inserts an eager table spool into the execution plan right after the index scan. That operator basically creates a copy of the rows in question and it uses tempdb for that.

The update portion of the MERGE statement has to follow the same rules and also uses a table spool in most cases where Halloween Protection is required.

While I can't tell if this is the case in your query, as I don't know the index definition(s), this is most likely what is happening here.