Why does an UPDATE against a table with an INSTEAD OF UPDATE trigger appear to do a clustered index insert, as well as a clustered index update? I'll start with a very simple example: two tables, both with the same schema, clustered on PK, but one of which has an INSTEAD OF UPDATE trigger:

CREATE TABLE Standard
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

CREATE TABLE InsteadOf
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

INSERT Standard (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
INSERT InsteadOf (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
GO

CREATE TRIGGER TR_InsteadOf_Update ON InsteadOf INSTEAD OF UPDATE
AS
BEGIN
    DECLARE @PK UNIQUEIDENTIFIER
    DECLARE @V INT
    DECLARE @cursor CURSOR
    SET @cursor = CURSOR FOR SELECT PK, V FROM Inserted
    OPEN @cursor

    FETCH NEXT FROM @cursor INTO @PK, @V
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE InsteadOf SET
            V = @V
        WHERE PK = @PK

        FETCH NEXT FROM @cursor INTO @PK, @V
    END
    CLOSE @cursor
    DEALLOCATE @cursor

END
GO


If I view the query plan for an update against the standard table, I get the expected clusted index update:

UPDATE Standard SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'




However if I perform a similar update against the table with the trigger, I get what appears to be a clustered index insert, as well as the clustered index update:

UPDATE InsteadOf SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'




Why is this? I can see the clustered index update I expected later in this query plan (query #4), but why do I get this extra insert at query #1?
An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example).



*The query processor internally renames the work table when constructing the user-visible form of the execution plan. When writing to it, it is renamed to the target base table, when reading, it is renamed as inserted or deleted more or less as people would expect to see in a trigger.

For more details, see my article Interesting things about INSTEAD OF triggers.