merge or delete then insert or delete then append when the database is unstable

Question

What are the best practices when it comes to modifying a huge (more than 500 M records) table in Azure SQL server in deployment.

By modification I mean the table gets updated for past few days (let's say 10 days) and the new data has to be written it to the huge. Note that the table already has some data for past 9 days in this example. In this case, I see these options to update the huge (target table):

1. Create a staging/temp (source) table with the last 10 days data and then merge, using the MERGE command, it to the target table. Here there is an issue that as there are many columns with no unique primary key, the comparison has to be done on multiple columns. In this case, does it make sense to use multiple columns in the join or use a single concatenated or hashed column created from all these columns?

2. delete the last 10 days of data and then insert the source table to target table. But I am bit cautious here since as this involves delete, the records are deleted first and then insert happens. What if the delete is successful and insert failed due to any database issue (may be due to a sudden downtime). Consider this as a deployed job where even if it fails for a day, it could recover in the next day's run. With MERGE command, if it fails, due to the roll back mechanism, the data is preserved at least.

3. delete the last 10 days of data and then append the table using databricks spark insert. Among the time consumption, this strategy was the best. However, as this too involves delete first, how to take care of the scenario if delete is successful and append fails.

4. truncate the entire table and then repopulate the entire table.

I see many advantages using MERGE but it take a long time for the operation to complete even with indexes are in place. The delete and append option gave the best timings however as the delete and append are independent operations, there is always a risk of losing the data as the append can fail. Is there a way to combine delete and insert in a single command so that it runs as a single block thereby ensuring that if any of these operation fail, it can rollback?

The SQL server is Microsoft SQL Azure (RTM) - 12.0.2000.8 

What are the best options suggested here? Thank you in advance for any answers/comments.

Answer 1

Besides what Erik said, which is excellent advice because MERGE can be both inefficient and buggy, here's some answers to your other questions:

What if the delete is successful and insert failed due to any database issue

That's the purpose of transactions:

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Also regarding:

In this case, does it make sense to use multiple columns in the join or use a single concatenated or hashed column created from all these columns?

If the number of columns exceed what's reasonable to create an index on (generally but arbitrarily about 5 or so columns) then it may make sense to create a hashed column on top of the concatenation of those columns.

Also, if the data types of those columns exceed the reasonable size of an index, 900 bytes max (e.g. if there's large string based columns like VARCHAR(1000)) then it also may make sense to create a hashed column on top of them.

You can create a HashKey column that leverages the HASHBYTES() function, which is deterministic, and therefore can be materialized on disk as a persisted computed column and even indexed accordingly (typically on their native key column(s) and the HashKey field). Then it becomes very easy and efficient to compare two of the same rows, to see if there's any changes.

Answer 2

This answer is to dissuade you from using merge. It’s like throwing SQL Server an awful knuckleball and requires a lot of special care and handling to use correctly.

• Use Caution with SQL Server's MERGE Statement

• Please Stop Using This UPSERT Anti-Pattern

• Mythbusting: Concurrent Update/Insert Solutions

• What To Avoid If You Want To Use MERGE

• UPSERT Requires a Unique Index

• SQL Server UPSERT Patterns and Antipatterns