sql-servert-sqlstored-procedureslog-shipping

MERGE vs DROP Table and Rebuild Indexes in SQL Server


I have a "log shipped" copy of a database that lives at a third party. Log shipping runs every 15 minutes at which time all connections to the database are dropped. The database is used for reporting purposes.

I have decided to pull some of the data from the log shipped (read only) database into a new database refreshed nightly. This will allow users to connect to the new database without risk of loosing connectivity due to log shipping. (It also allows more granular security permissions to be used, since the read-only copy can't be edited)

I can think of 2 patterns to accomplish this.

  1. Drop Table, Create Table, Create Indexes
  2. use the MERGE statement to insert/update/delete records

I have implemented the solution using method 1 above, and it works just fine.

It feels a bit heavy to me to drop all of the data every day. Are there any side effects to method 1 above that should push me over to using method 2?

To provide a sense of scale, I am only syncing 3 tables,

Table 1 - 38 columns - 13,110 rows
Table 2 - 82 columns - 17,421 rows
Table 3 - 22 columns - 249 rows

The resulting database is ~1.3 GB. (There are some other tables in there as well)

I would appreciate guidance on Method 1 vs 2, and whether there is another method that I am not thinking about.


Solution

  • TRUNCATE and INSERT would be more efficient than either dropping or merging.