SQL Merge SOURCE order and OUTPUT order-CodePudding

Consider an SQL MERGE statement merging 4 rows using a derived table into Target like so:

MERGE Target AS T USING 
(VALUES 
  (?, ?, ?), 
  (?, ?, ?),
  (?, ?, ?),
  (?, ?, ?),
) as S(Account_ID,Device_ID,Device_Name)

ON T.Account_ID = S.Account_ID

WHEN MATCHED THEN UPDATE SET  
  Account_ID = S.Account_ID, 
  Device_ID = S.Device_ID, 
  Device_Name = S.Device_Name

WHEN NOT MATCHED THEN INSERT 
  (Account_ID,Device_ID,Device_Name)
  VALUES (S.Account_ID,S.Device_ID,S.Device_Name)

OUTPUT $action, inserted.*;

Does the output (of inserted rows) respect the same order that is specified in the derived table? For example, assume rows 2 and 4 were inserted into Target. Does the output result maintain the same order like so [2,4].

For the tests that I performed, it seems to do it, but I could not find any reliable documentation around it.

If it helps Target has an auto-incrementing primary key as well.

CodePudding user response：

The documentation on OUTPUT explicitly states:

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

Which makes perfect sense, as there is never a guarantee how any query or DML is executed. the compiler is free to reorder things or parallelize, as long as the semantics are the same.

More to the point: the MERGE itself is not guaranteed to insert or update in any particular order. This is irrespective of any clustered or no-clustered indexes, primary keys or IDENTITY columns.

CodePudding user response：

As many responses have pointed out, the MERGE operation does not guarantee any ordering for the upserted rows.

However, the approach outlined here worked for my use case by specifying an incremental index (SourceId) for the rows and then doing some in-memory processing (in my NodeJS code) to sort the OUTPUT response by the SourceId.

MERGE Target AS T USING 
(VALUES 
  (1, ?, ?, ?), 
  (2, ?, ?, ?),
  (3, ?, ?, ?),
  (4, ?, ?, ?),
) as S(SourceId, Account_ID,Device_ID,Device_Name)

ON T.Account_ID = S.Account_ID

WHEN MATCHED THEN UPDATE SET  
  Account_ID = S.Account_ID, 
  Device_ID = S.Device_ID, 
  Device_Name = S.Device_Name

WHEN NOT MATCHED THEN INSERT 
  (Account_ID,Device_ID,Device_Name)
  VALUES (S.Account_ID,S.Device_ID,S.Device_Name)

OUTPUT $action, S.SourceId, inserted.*;