sql-server – 依赖INSERT的OUTPUT子句的顺序是否安全？

CREATE TABLE dbo.Target (
   TargetId int identity(1,1) NOT NULL,Color varchar(20) NOT NULL,Action varchar(10) NOT NULL,-- of course this should be normalized
   Code int NOT NULL,CONSTRAINT PK_Target PRIMARY KEY CLUSTERED (TargetId)
);

在两个略有不同的场景中,我想插入行并返回标识列中的值.

情景1

INSERT dbo.Target (Color,Action,Code)
OUTPUT inserted.TargetId
SELECT t.Color,t.Action,t.Code
FROM
   (VALUES
      ('Blue','New',1234),('Blue','Cancel',4567),('Red',5678)
   ) t (Color,Code)
;

场景2

CREATE TABLE #Target (
   Color varchar(20) NOT NULL,Code int NOT NULL,PRIMARY KEY CLUSTERED (Color,Action)
);

-- Bulk insert to the table the same three rows as above by any means

INSERT dbo.Target (Color,t.Code
FROM #Target
;

题

我可以依赖于dbo.Target表插入中返回的标识值,按照它们在1)VALUES子句和2)#Target表中存在的顺序返回,这样我就可以根据它们在输出行集中的位置来关联它们到原始输入？

以供参考

下面是一些精简的C#代码,用于演示应用程序中发生的情况(方案1,很快将转换为使用SqlBulkCopy)：

public IReadOnlyCollection<Target> InsertTargets(IEnumerable<Target> targets) {
   var targetList = targets.ToList();
   const string insertSql = @"
      INSERT dbo.Target (
         CoreItemId,TargetDateTimeUtc,TargetTypeId,)
      OUTPUT
         Inserted.TargetId
      SELECT
         input.CoreItemId,input.TargetDateTimeUtc,input.TargetTypeId,FROM
         (VALUES
            {0}
         ) input (
            CoreItemId,TargetTypeId
         );";
   var results = Connection.Query<DbTargetInsertResult>(
      string.Format(
         insertSql,string.Join(
            ",",targetList
               .Select(target => $@"({target.CoreItemId
                  },'{target.TargetDateTimeUtc:yyyy-MM-ddTHH:mm:ss.fff
                  }',{(byte) target.TargetType
                  })";
               )
         )
      )
      .ToList();
   return targetList
      .Zip( // The correlation that relies on the order of the two inputs being the same
         results,(inputTarget,insertResult) => new Target(
            insertResult.TargetId,// with the new TargetId to replace null.
            inputTarget.TargetDateTimeUtc,inputTarget.CoreItemId,inputTarget.TargetType
         )
      )
      .ToList()
      .AsReadOnly();
}

解决方法

Can I rely on the returned identity values from the dbo.Target table
insert to be returned in the order they existed in the 1) VALUES
clause and 2) #Target table,so that I can correlate them by their
position in the output rowset back to the original input?

不,如果没有实际的文件保证,您不能依赖任何事情.文档explicitly states没有这样的保证.

SQL Server does not guarantee the order in which rows are processed
and returned by DML statements using the OUTPUT clause. It is up to
the application to include an appropriate WHERE clause that can
guarantee the desired semantics,or understand that when multiple rows
may qualify for the DML operation,there is no guaranteed order.

这将依赖于许多无证假设

>从常量扫描输出行的顺序与values子句的顺序相同(我从未见过它们不同但AFAIK不保证这一点).
>插入行的顺序与从常量扫描输出的顺序相同(绝对不总是如此).
>如果使用“宽”(每个索引)执行计划,则输出子句中的值将从聚集索引更新运算符中提取,而不是从任何二级索引的值中提取.
>此后保证订单保留 – 例如当packaging rows up for transmission over the network.
>即使订单看起来可预测,现在对并行插入等功能的实施更改也不会改变未来的订单(目前为if the OUTPUT clause is specified in the INSERT…SELECT statement to return results to the client,then parallel plans are disabled in general,including INSERTs)

如果向VALUES子句添加600行,则可以看到第二点失败的示例(假设为(颜色,动作)的聚类PK).然后计划在插入之前有一个排序运算符,因此在VALUES子句中丢失了原始顺序.

有一种记录的实现目标的方法,这是为源添加编号并使用MERGE而不是INSERT

MERGE dbo.Target
USING (VALUES (1,'Blue',(2,(3,'Red',5678) ) t (SourceId,Color,Code)
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Color,Code)
  VALUES (Color,Code)
OUTPUT t.SourceId,inserted.TargetId;

@a_horse_with_no_name

Is the merge really necessary? Couldn’t you just do an insert into ...
select ... from (values (..)) t (...) order by sourceid?

是的,你可以. Ordering guarantees in SQL Server…表示

INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which
the rows are inserted

所以你可以使用

INSERT dbo.Target (Color,t.Code
FROM
(VALUES (1,Code)
ORDER BY t.SourceId

这将保证以t.SourceId的顺序分配标识值,但不保证它们以任何特定顺序输出或者分配的标识列值没有间隙(例如,如果尝试并发插入).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!