SQL MERGE语句中的UPDATE-no-op是什么意思？

Question

我有一个包含持久数据的表格。现在，当我查询它时，我还有一个非常复杂的公用表达式（CTE），用于计算结果所需的值，并且我需要将缺失的行插入到持久表中。最终，我希望选择由 CTE 确认的所有行组成的结果，但如果它们已经在表格中，则使用来自表格的数据，并且我需要知道一行是否刚刚被插入。

简化后，此操作如下（如果您想尝试运行以下代码，请将其作为正常查询运行）：

-- Set-up of test data, this would be the persisted table 
DECLARE @target TABLE (id int NOT NULL PRIMARY KEY) ;
INSERT INTO @target (id) SELECT v.id FROM (VALUES (1), (2)) v(id);

-- START OF THE CODE IN QUESTION
-- The result table variable (will be several columns in the end)
DECLARE @result TABLE (id int NOT NULL, new bit NOT NULL) ;

WITH Source AS (
    -- Imagine a fairly expensive, recursive CTE here
    SELECT * FROM (VALUES (1), (3)) AS Source (id)
)
MERGE INTO @target AS Target
    USING Source
    ON Target.id = Source.id
    -- Perform a no-op on the match to get the output record
    WHEN MATCHED THEN 
        UPDATE SET Target.id=Target.id
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (id) VALUES (SOURCE.id)
    -- select the data to be returned - will be more columns
    OUTPUT source.id, CASE WHEN $action='INSERT' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END
      INTO @result ;

-- Select the result
SELECT * FROM @result;

我不喜欢WHEN MATCHED THEN UPDATE这部分，我宁愿去掉冗余的更新，但这样就无法在OUTPUT子句中获取结果行了。

这是完成和返回数据最有效的方式吗？

还是说有更有效的解决方案，例如通过使用SELECT预先计算结果，然后执行将new=0的行插入的操作，而不使用MERGE？由于查询计划基本上归结为"Clustered Index Merge"，与单独的SELECT后跟INSERT变体相比，在性能上对我来说不太清楚。并且我想知道SQL Server（2008 R2 with CU1）是否真的足够聪明，能够看出UPDATE是一个无操作（例如，并不需要写入）。

- Lucero

---

我有和你一样的设置，使用with、merge以及一个冗余的update，并且也在寻找一种不会实际执行更新操作但仍然能返回ID的解决方案。如果你找到了解决方案，请添加上来 =) - David Mårtensson

回复：不更新的更新 如果可能的话，您最好对非聚集键的列执行No-OP更新，如此处所述http://sqlblog.com/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx - Martin Smith

@David Mårtensson，感谢您添加悬赏奖励。 :) - Lucero

@Lucero - 让我看看我是否理解你的意思。你想将一堆行插入到 @target 中，并输出被插入到 @result 中的行。是这样吗？ - Thomas

如果我理解正确，他想要插入的ID和已存在行的ID，这就是合并的作用。如果没有合并，您将无法获得已经存在的其他行的ID。问题的重点是能否在不实际更新的情况下完成，因为这会影响性能。 - David Mårtensson

@Thomas，David说得对，我需要在输出中包含新的和现有的ID。 - Lucero

Answer 1

你可以声明一个虚拟变量，并在 WHEN MATCHED 子句中设置它的值。

 DECLARE @dummy int;
 ...
 MERGE
 ...
 WHEN MATCHED THEN
   UPDATE SET @dummy = 0
 ...

我认为这应该比实际的表格更新成本更低。

- Andriy M

---

这是一个非常棒的想法，看起来效果不错。除非有更好的解决方案出现，否则我会接受这个答案……似乎可以将“MERGE”所需时间缩短三分之一。 - Lucero

这个解决方案至少解决了我的问题，值得花费100声望奖励=）感谢Andriy和Lucero的答案以及好问题和示例。 - David Mårtensson

@David，谢谢！这确实是一个好问题。像这样的事情通常值得提前知道，而不是等到真正需要必要知识的时候再去解决。 - Andriy M