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?