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
Best Answer
(Expanding on my comment on the question.)
Without a unique constraint on the combination of
AreaId
andParameterTypeId
, the given code is broken because@UpdatedId = target.Id
will only ever record a single rowId
.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.