]
07-31-2023, 07:25 AM
If you're doing this at any sort of a frequency, heck even on a schedule, I would **absolutely, unequivocally never use a DML statement.** The cost of writing to the transaction log is just to high, and setting the entire database into `SIMPLE` recovery mode to truncate one table is ridiculous.
The best way, is unfortunately the hard or laborious way. That being:
- Drop constraints
- Truncate table
- Re-create constraints
My process for doing this involves the following steps:
1. In SSMS right-click on the table in question, and select *View Dependencies*
2. Take note of the tables referenced (if any)
3. Back in object explorer, expand the *Keys* node and take note of the foreign keys (if any)
4. Start scripting (drop / truncate / re-create)
Scripts of this nature *should* be done within a `begin tran` and `commit tran` block.
The best way, is unfortunately the hard or laborious way. That being:
- Drop constraints
- Truncate table
- Re-create constraints
My process for doing this involves the following steps:
1. In SSMS right-click on the table in question, and select *View Dependencies*
2. Take note of the tables referenced (if any)
3. Back in object explorer, expand the *Keys* node and take note of the foreign keys (if any)
4. Start scripting (drop / truncate / re-create)
Scripts of this nature *should* be done within a `begin tran` and `commit tran` block.