SQL Server Update Statement joining on index column

Question

Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this:

DELETE  FROM MyTable
WHERE   EXISTS (   SELECT   1
                   FROM     StagingMyTable s
                   WHERE    MyTable.ID = s.ID
                            AND MyTable.ID2 = s.ID2)

INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable

which was changed to a Update and Insert process like this:

UPDATE  mt
SET     mt.ID = s.ID
      , mt.ID2 = s.ID2
      , mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
        and mt.ID2 = s.ID2


INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable s
WHERE   NOT EXISTS (   SELECT   1
                       FROM     MyTable
                       WHERE    MyTable.ID = s.ID
                                AND MyTable.ID2 = s.ID2)

There is a clustered index on MyTable on the ID and ID2 columns which is why we decided to update the stored procedure to update and insert. What I want to know is whether or not I should leave out the two key columns on the update statement even though those values aren't being changed i.e.

UPDATE  mt
SET     mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
                         and mt.ID2 = s.ID2

If I include the key columns in the update statement, does SQL Server thinks these will be new values and delete and re-insert the rows anyway?

Answer 1

SQL server may choose to perform an in-place update or an insert and a delete depending on many factors and you shouldn’t worry about it. Since the keys are not being updated I would leave them out of the SET clause to help the optimizer make the best decision.

If you are using SQL Server 2008 or later, look at the MERGE statement which can replace both operations: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

HTH

Answer 2

if ID and ID2 columns are key columns then you need to worry about that faulty Update Statement.

Agree, those values aren't being changed but it create very expensive Query Plan.

In Fact you should Compare the query Plan with and without mt.col1 = s.col1 , mt.col2 = s.col2 in Update your Self.

Begin Tran

UPDATE  mt
SET     mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
                         and mt.ID2 = s.ID2

If (@@TranCount>0)
RollBack

You will Notice Split Operator which is part of split-sort-collapse pattern

If I include the key columns in the update statement, does SQL Server thinks these will be new values and delete and re-insert the rows anyway?

Yes, Execution plan reveal so.There is split-sort-collapse operator in Execution Plan which exactly does same thing (delete and re-insert the rows ).