依賴 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', 'New', 5678)
  ) t (Color, Action, Code)
;

方案 2

CREATE TABLE #Target (
  Color varchar(20) NOT NULL,
  Action varchar(10) 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, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, 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,
           TargetDateTimeUtc,
           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();
}

我是否可以依賴從 dbo.Target 表插入返回的標識值按照它們在 1) VALUES 子句和 2) #Target 表中存在的順序返回，以便我可以通過它們在輸出行集中的位置將它們關聯回來到原始輸入？

不，如果沒有實際的書面保證，您就不能依賴任何保證。該文件明確指出沒有這樣的保證。

SQL Server 不保證使用 OUTPUT 子句的 DML 語句處理和返回行的順序。由應用程序包含一個適當的 WHERE 子句來保證所需的語義，或者理解當多行可能符合 DML 操作的條件時，沒有保證的順序。

這將依賴於許多未記錄的假設

1. 從恆定掃描中輸出行的順序與 values 子句的順序相同（我從未見過它們不同，但 AFAIK 不能保證這一點）。
2. 插入行的順序將與它們從恆定掃描中輸出的順序相同（絕對並非總是如此）。
3. 如果使用“寬”（每個索引）執行計劃，則輸出子句中的值將從聚集索引更新運算符中提取，而不是從任何二級索引中提取。
4. 保證以後保留該順序 - 例如，在打包行以通過網路傳輸時。
5. 即使訂單現在看起來是可預測的，對並行插入等功能的實現更改也不會在未來更改訂單（目前如果在 INSERT…SELECT 語句中指定了 OUTPUT 子句以將結果返回給客戶端，那麼並行計劃是一般禁用，包括 INSERTs）

如果向子句(Color, Action)添加 600 行，則可以看到第二點失敗的範例（假設 PK 的集群）。然後計劃在插入之前有一個排序運算符，因此會失去子句VALUES中的原始順序。VALUES

不過，有一個記錄在案的方法可以實現您的目標，這是在原始碼中添加編號並使用MERGE而不是INSERT

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

@a_horse_with_no_name

合併真的有必要嗎？你不能做一個insert into ... select ... from (values (..)) t (...) order by sourceid嗎？

是的，你可以。SQL Server 中的排序保證……聲明

使用帶有 ORDER BY 的 SELECT 來填充行的 INSERT 查詢保證了標識值的計算方式，但不保證插入行的順序

所以你可以使用

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM
(VALUES (1, 'Blue', 'New', 1234),
       (2, 'Blue', 'Cancel', 4567),
       (3, 'Red', 'New', 5678) ) t (SourceId, Color, Action, Code)
ORDER BY t.SourceId

這將保證標識值按順序分配，t.SourceId但不能保證它們以任何特定順序輸出，或者分配的標識列值沒有間隙（例如，如果嘗試並發插入）。

引用自：https://dba.stackexchange.com/questions/155735