I have this table in a SQL Server 2008 R2 instance which I have a scheduled process that runs nightly against it. The table can have upward to 500K records in it at any one time. After processing this table I need to remove all rows from it so I am wondering which of the following methods would produce the least overhead (ie Excessive Transaction Log entries):
我在SQL Server 2008 R2实例中有这个表，我有一个计划的进程，每晚运行它。该表一次最多可以有500K记录。处理完这个表后，我需要从中删除所有行，所以我想知道以下哪种方法会产生最少的开销（即过多的事务日志条目）：
- Truncate Table
- 截断表
- Drop and recreate the table
- 删除并重新创建表格
Deleting the contents of the table is out due to time and extra Transaction log entries it makes.
删除表的内容是由于时间和它所做的额外事务日志条目。
The consensus seems to be Truncation, Thanks everyone!
共识似乎是截断，谢谢大家！
4 个解决方案
#1
8
Truncating the table does not leave row-by-row entries in the transaction log - so neither solution will clutter up your logs too much. If it were me, I'd truncate over having to drop and create each time.
截断表不会在事务日志中留下逐行条目 - 因此这两种解决方案都不会使日志过于混乱。如果是我，我会因为不得不每次都放弃而创造。
#2
13
TRUNCATE TABLE is your best bet. From MSDN:
TRUNCATE TABLE是你最好的选择。来自MSDN：
Removes all rows from a table without logging the individual row deletes.
从表中删除所有行而不记录单个行删除。
So that means it won't bloat your transaction log. Dropping and creating the table not only requires more complex SQL, but also additional permissions. Any settings attached to the table (triggers, GRANT or DENY, etc.) will also have to be re-built.
这意味着它不会破坏您的事务日志。删除和创建表不仅需要更复杂的SQL，还需要其他权限。附加到表格的任何设置（触发器，GRANT或DENY等）也必须重新构建。
#3
5
I would go for TRUNCATE TABLE. You can potentially have overheads when indexes, triggers, etc get dropped. Plus you will lose permissions which will also have to be re-created along with any other required objects required for that table.
我会选择TRUNCATE TABLE。当索引，触发器等被丢弃时，您可能会有开销。此外，您将失去权限，这些权限也必须与该表所需的任何其他必需对象一起重新创建。
Also on DROP TABLE in MDSN below it mentions a little gotcha if you execute DROP and CREATE TABLE in the same batch
同样在下面的MDSN中的DROP TABLE上，如果您在同一批次中执行DROP和CREATE TABLE，则会提到一些问题
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
不应在同一批次的同一个表上执行DROP TABLE和CREATE TABLE。否则可能会发生意外错误。
#4
2
Dropping the table will destroy any associated objects (indexes, triggers) and may make procedures or views invalid. I would go with truncate, since it won't blow up your log and causes none of the possible issues a drop and create does.
删除表将破坏任何关联的对象（索引，触发器），并可能使过程或视图无效。我会选择truncate，因为它不会炸毁你的日志，并且不会导致drop和create之类的问题。
#1
8
Truncating the table does not leave row-by-row entries in the transaction log - so neither solution will clutter up your logs too much. If it were me, I'd truncate over having to drop and create each time.
截断表不会在事务日志中留下逐行条目 - 因此这两种解决方案都不会使日志过于混乱。如果是我，我会因为不得不每次都放弃而创造。
#2
13
TRUNCATE TABLE is your best bet. From MSDN:
TRUNCATE TABLE是你最好的选择。来自MSDN：
Removes all rows from a table without logging the individual row deletes.
从表中删除所有行而不记录单个行删除。
So that means it won't bloat your transaction log. Dropping and creating the table not only requires more complex SQL, but also additional permissions. Any settings attached to the table (triggers, GRANT or DENY, etc.) will also have to be re-built.
这意味着它不会破坏您的事务日志。删除和创建表不仅需要更复杂的SQL，还需要其他权限。附加到表格的任何设置（触发器，GRANT或DENY等）也必须重新构建。
#3
5
I would go for TRUNCATE TABLE. You can potentially have overheads when indexes, triggers, etc get dropped. Plus you will lose permissions which will also have to be re-created along with any other required objects required for that table.
我会选择TRUNCATE TABLE。当索引，触发器等被丢弃时，您可能会有开销。此外，您将失去权限，这些权限也必须与该表所需的任何其他必需对象一起重新创建。
Also on DROP TABLE in MDSN below it mentions a little gotcha if you execute DROP and CREATE TABLE in the same batch
同样在下面的MDSN中的DROP TABLE上，如果您在同一批次中执行DROP和CREATE TABLE，则会提到一些问题
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
不应在同一批次的同一个表上执行DROP TABLE和CREATE TABLE。否则可能会发生意外错误。
#4
2
Dropping the table will destroy any associated objects (indexes, triggers) and may make procedures or views invalid. I would go with truncate, since it won't blow up your log and causes none of the possible issues a drop and create does.
删除表将破坏任何关联的对象（索引，触发器），并可能使过程或视图无效。我会选择truncate，因为它不会炸毁你的日志，并且不会导致drop和create之类的问题。