Início / dba / Perguntas / 155735
Accepted
ErikE
Asked: 2016-11-19 12:03:59 +0800 CST

É seguro confiar na ordem da cláusula OUTPUT de um INSERT?

  • 772

Dada esta tabela:

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)
);

Em dois cenários ligeiramente diferentes, desejo inserir linhas e retornar os valores da coluna de identidade.

Cenário 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)
;

Cenário 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
;

Pergunta

Posso confiar que os valores de identidade retornados da dbo.Targetinserção da tabela serão retornados na ordem em que existiam na VALUEScláusula 1) e na #Targettabela 2), para que eu possa correlacioná-los por sua posição no conjunto de linhas de saída de volta à entrada original?

Para referência

Aqui está um código C# reduzido que demonstra o que está acontecendo no aplicativo (cenário 1, que será convertido em breve para usar 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();
}
sql-server sql-server-2012

1 respostas

  1. Best Answer

    Posso confiar nos valores de identidade retornados da inserção da tabela dbo.Target para serem retornados na ordem em que existiam na cláusula 1) VALUES e 2) tabela #Target, para que eu possa correlacioná-los por sua posição no conjunto de linhas de saída de volta à entrada original?

    Não, você não pode confiar em nada para ser garantido sem uma garantia real documentada. A documentação afirma explicitamente que não existe tal garantia.

    O SQL Server não garante a ordem na qual as linhas são processadas e retornadas por instruções DML usando a cláusula OUTPUT. Cabe ao aplicativo incluir uma cláusula WHERE apropriada que possa garantir a semântica desejada ou entender que, quando várias linhas podem se qualificar para a operação DML, não há ordem garantida.

    Isso dependeria de muitas suposições não documentadas

    1. A ordem em que as linhas são geradas a partir da verificação constante está na mesma ordem da cláusula de valores (nunca vi diferenças, mas AFAIK isso não é garantido).
    2. A ordem em que as linhas são inseridas será a mesma que a ordem em que são exibidas na varredura constante (definitivamente nem sempre é o caso).
    3. Se estiver usando um plano de execução "amplo" (por índice), os valores da cláusula de saída serão extraídos do operador de atualização de índice clusterizado e não de quaisquer índices secundários.
    4. Que a ordem seja preservada posteriormente - por exemplo, ao empacotar linhas para transmissão pela rede .
    5. Mesmo que a ordem pareça previsível agora, as mudanças de implementação em recursos como inserção paralela não alterarão a ordem no futuro (atualmente , se a cláusula OUTPUT for especificada na instrução INSERT…SELECT para retornar resultados ao cliente, os planos paralelos serão desativado em geral, incluindo INSERTs )

    Um exemplo de falha do ponto dois (assumindo PK clusterizado de (Color, Action)) pode ser visto se você adicionar 600 linhas à VALUEScláusula. Em seguida, o plano possui um operador de classificação antes da inserção, perdendo sua ordem original na VALUEScláusula.

    Existe uma maneira documentada de atingir seu objetivo e isso é adicionar uma numeração à fonte e usar MERGEem vez deINSERT

    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;

    insira a descrição da imagem aqui

    @um cavalo sem nome

    A fusão é realmente necessária? Você não poderia simplesmente fazer um insert into ... select ... from (values (..)) t (...) order by sourceid?

    Sim você pode. Ordenar garantias no SQL Server… afirma que

    Consultas INSERT que usam SELECT com ORDER BY para preencher linhas garantem como os valores de identidade são calculados, mas não a ordem em que as linhas são inseridas

    Então você poderia usar

    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

    insira a descrição da imagem aqui

    Isso garantiria que os valores de identidade sejam atribuídos em ordem, t.SourceIdmas não que sejam exibidos em qualquer ordem específica ou que os valores de coluna de identidade atribuídos não tenham lacunas (por exemplo, se uma inserção simultânea for tentada).

    • 30

relate perguntas