Is it better to delete then insert, or to update then insert in SQL server?

Question

I'm using SQL server 2005. I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id.

I have a list of ids in a table variable, and I want to update the table with the current time for all the ids that are already in the table, and insert new records for the ids that are not yet in the table. SQL 2005 doesn't have a MERGE statement or a ON DUPLICATE KEY UPDATE.

Which of these forms is preferred:

UPDATE IdTimes SET LastEventTime = GetDate() WHERE Id in (SELECT NewId FROM @NewIds)

INSERT INTO IdTimes(Id, LastEventTime)
   SELECT NewId, GetDate() FROM @NewIds
   WHERE NOT EXISTS (SELECT Id FROM IdTimes WHERE NewId = Id)

Or:

DELETE FROM IdTimes WHERE Id IN (SELECT NewId FROM @NewIds)
INSERT INTO IdTimes(Id, LastEventTime) SELECT NewId, GetDate() FROM @NewIds

Or is there a better solution to achieve this?

Answer 1

UPDATE then INSERT usually. Simply, it's less work.

In this case, you have an ID columns (IDENTITY): I'll assume this is the clustered index

You delete rows, you leave gaps in pages = fragmentation. You add rows, you probably need more pages allocated. Other processes are doing this too.

An UPDATE will update in-situ and you'll have a less expensive INSERT because there is less rows.

Saying that...

If your new:update is 100:1 then it doesn't really matter of course. And the EXISTS is required too.

However, from a raw "shifting data" perspective UPDATE..INSERT would be my choice