SQL MERGE 语句中的 UPDATE-no-op [英] UPDATE-no-op in SQL MERGE statement

问题描述

我有一个包含一些持久数据的表.现在，当我查询它时，我还有一个非常复杂的 CTE，它计算结果所需的值，我需要将缺失的行插入到持久表中.最后，我想选择由 CTE 标识的所有行组成的结果，但如果它们已经在表中，则包含表中的数据，并且我需要是否刚刚插入行的信息.

I have a table with some persistent data in it. Now when I query it, I also have a pretty complex CTE which computes the values required for the result and I need to insert missing rows into the persistent table. In the end I want to select the result consisting of all the rows identified by the CTE but with the data from the table if they were already in the table, and I need the information whether a row has been just inserted or not.

简化后的工作方式如下(如果您想尝试，以下代码将作为普通查询运行):

Simplified this works like this (the following code runs as a normal query if you like to try it):

-- 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 子句.

I don't like the WHEN MATCHED THEN UPDATE part, I'd rather leave the redundant update away but then I don't get the result row in the OUTPUT clause.

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

Is this the most efficient way to do this kind of completing and returning data?

或者如果没有 MERGE 是否会有更有效的解决方案，例如使用 SELECT 预先计算结果，然后执行 INSERT new=0 的行?我很难解释查询计划，因为它基本上归结为聚集索引合并"，与单独的 SELECT 后跟 INSERT 相比，这在性能方面对我来说非常模糊变体.我想知道 SQL Server(带有 CU1 的 2008 R2)是否真的足够聪明，可以看到 UPDATE 是空操作(例如，不需要写入).

Or would there be a more efficient solution without MERGE, for instance by pre-computing the result with a SELECT and then perform an INSERT of the rows which are new=0? I have difficulties interpreting the query plan since it basically boils down to a "Clustered Index Merge" which is pretty vague to me performance-wise compared to the separate SELECT followed by INSERT variant. And I wonder if SQL Server (2008 R2 with CU1) is actually smart enough to see that the UPDATE is a no-op (e.g. no write required).

推荐答案

您可以声明一个虚拟变量并在 WHEN MATCHED 子句中设置其值.

You could declare a dummy variable and set its value in the WHEN MATCHED clause.

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

我相信它应该比实际的表更新更便宜.

I believe it should be less expensive than the actual table update.

这篇关于SQL MERGE 语句中的 UPDATE-no-op的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！