Why do upserted INSERTED.ID and DELETED.ID both contain values after MERGE?

Question

Surprisingly, I can't find an answer to this on Google, although my terminology may be off. I also didn't see an explanation on MDSN.

Take the following code to perform a simple MERGE:

DECLARE @tbl_1 TABLE (ID int IDENTITY(1,1), person nvarchar(20));
DECLARE @tbl_2 TABLE (ID int IDENTITY(1,1), person nvarchar(20));   
INSERT INTO @tbl_1 (person) VALUES ('Bob'),('Ted'),('Brian');
INSERT INTO @tbl_2 (person) VALUES ('Bob'),('Ted'),('Peter');

MERGE INTO  
    @tbl_2 as tgt
USING 
    @tbl_1 as src
ON
    (tgt.person = src.person)
WHEN MATCHED THEN 
    UPDATE SET tgt.person = src.person
WHEN NOT MATCHED BY TARGET THEN
    INSERT (person) VALUES (src.person)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION,
    DELETED.ID,
    DELETED.person AS PersonOld,
    INSERTED.ID,
    INSERTED.person AS PersonNew;

In the results, I see that the ID value of each row is shown against both INSERTED and DELETED rows, where an UPDATE has taken place:

Why is this please? I would expect DELETED.ID to be NULL after an update, with INSERTED.ID representing the UPSERTED row (I have worked with triggers in the past, and assumed MERGE would follow the same approach).

Answer 1

Because MERGE as you seem to be aware is an UPSERT (update + insert).

Now, the INSERTED tables record information for rows added by INSERT & UPDATE comands and the DELETED table contains information for rows that were either updated or deleted.

Taking a look at the MSDN documentation on how to "Use the inserted and deleted Tables":

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

and

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

---

Update:

I saw you commented to your question that you realized that the operation is actually a delsert under the hood. And you might be thinking why would that be?

Think about how data is stored in SQL Server. It's stored on 8KB pages and when you update information in a column that is contained in a data page, the entire data page is being re-written, so essentially a delsert.

And the same thing with an INSERT, a new row will go in a data page (and might generate a page split - but that's another subject) and that entire data page will have to be re-written.