Sql-server – SQL Server Update Statement joining on index column

indexsql servert-sqlupdate

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?

Best Answer

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://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

HTH

Related Question