DROP vs DELETE vs TRUNCATE for staging data I need to import data (>> 1 GB) into MS-SQL-Server. For several reasons I need to import this data into a staging table (not directly into the target). From staging the data is loaded into the target table. The data in the staging table is never used again. I have several ways to do:


Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
Delete all data in staging, load the data into staging table, load all data into target.
Truncate staging table, load into staging, load into target
Drop staging table, create staging table, laod into staging table, load into target.
Drop staging database, create staging table, load into staging table, load into target.


If the solution is not restricted  by your server role: What would be the recomended solution? Which is most robust?

Solution 1 is most likely nonsense if you don't need the data anymore. Solution 3 performances much better compared to 1 and 2. But what between solution 4 and 3? Actually I would prefer solution 5 as I can change nealy every parameter within the staging process within my load procedure but you need rights to drop&amp;create database. This is quite a lot for a load process. So in most of the cases I use drop and recreate the staging table. But is this the recommended way?
The "recommended way" is to design the best solution given the relevant factors.

Options #1 and #2 are unlikely to be ideal as they result in a lot of unnecessary I/O.  #3 is what would be best in most situations, but as far as I/O is concerned it is not much different from #4.  So performance-wise, #3 and #4 can considered to be the same.  The additional I/O to drop/create a table as opposed to just truncating it is insignificant compared to the I/O of the typical data load.

Your comments seem to indicate that you need to make changes to the staging table quite often, and if that is the case, then #4 may be best for you.  Since it's a staging table, it is unlikely that permissions are complex, and if it's in a dedicated staging database where staging account is the only ID that has read/write, then you wouldn't even need to explicitly set permissions when the table is created.

Option #5 is a non-starter unless you have some specific reason to create a new database each time.