What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column.

Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are changing and lets say these are not part of any non-clustered indexes, then you may be better off "Updating" the records because in this case only the clustered index will be updated (and indexes will not have to be updated).

---

On further research, I did find that the above comment by me is sort of redundant as SQL Server internally has 2 separate mechanism for performing an UPDATE. - An "in-place update" (ie by changing a columns value to a new in the original row) or as a "not-in-place UPDATE" (DELETE followed by an INSERT).

In place updates are the rule and are performed if possible. Here the rows stay exactly at the same location on the same page in the same extent. Only the bytes affected are chnaged. The tlog only has one record (provided there are no update triggers). Updates happen in place if a heap is being updated (and there is enough space on the page). Updates also happen in place if the clustering key changes but the row does not need to move at all.

For eg: if you have a clustered index on last name and you have the names: Able, Baker, Charlie Now you want to update Baker to Becker. No rows have to be moved. So this can take in-place. Whereas, if you have to update Able to Kumar, the rows will have to be shifted (even though they will be on the same page). In this case, SQL Server will do a DELETE followed by an INSERT.

Considering the above, I would suggest that you do a normal UPDATE and let SQL Server figure out the best way to how to do it internally.

For more details on "UPDATE" internals or for that matter any SQL Server related internals, check out Kalen Delaney, Paul Randal's, et al.'s book - SQL Server 2008 Internals.

Have you investigated the MERGE command in SQL 2008? Here is a basic example:

  merge YourBigTable ybt
  using (select distinct (RecordID) from YourOtherTable) yot
     on yot.Recordid = YBT.RecordID
  when NOT matched by target
  then  insert (RecordID)
        values (yot.DeviceID) ;

This is basically an "UPSERT" command. Update if it exists, insert it if it does not. VERY fast, very cool command.

But, I myself checked the Delete and Insert vs Update on a table that has 30million (3crore) records. This table has one clustered unique composite key and 3 Nonclustered keys. For Delete & Insert, it took 9 min. For Update it took 55 min. There is only one column that was updated in each row.

So, I request you people to not guess. The equations will change when dealing with large table with many columns and with much data.