MERGE vs TRUNCATE and INSERT

Question

I recently upgraded to MSSQL 2008 R2 and wondered if the "new" Merge function was more efficient / faster than the Truncate and Insert function for updating tables, each table has millions of rows. If someone could provide some evidence of their effectiveness, that would be great, but any explanation would be helpful!

Answer 1

Not sure if I understood your point TRUNCATE and INSERT correctly. If not, feel free to correct me.

MERGE meant as a mechanism to execute either UPDATE for an existing row, or, if no existing row is found, INSERT .

You suggest TRUNCATE and INSERT , which will remove the use of MERGE, since everything will be INSERT. I don’t even know if MERGE will MERGE faster or not in this case, because I have never tried it, but I would suggest that the MERGE part that determines whether to use UPDATE or INSERT imposes some overhead.

Answer 2

I used MERGE because I needed to track data changes (keeping the checksum of the rows), but since it seems like you just need these “cloned” data every time, I don’t see any special reasons for using MERGE , because MERGE performs some checks (primary keys and additional conditions on each WHEN are checked there); using TRUNCATE and then an INSERT array, you do not have to have all the conditions in the game

By the way, do not consider this 100% true, I do not have a performance test to cite as evidence; I suggest you try both operations and see which one takes longer =)