批量删除（截断 vs 删除）

Question

我们有一个拥有超过1.5亿条记录的表格。我们需要清空/删除所有行。由于其写入了t-logs，删除操作将耗时很长，而且我们无法更改整个DB的恢复模型。我们已经测试了截断表选项。 我们意识到，截断会从表格中取消分配页面，如果我没错的话，这些页面会变成可重用状态，但不会自动缩小数据库的大小。所以，如果我们想要减少数据库的大小，我们真正需要做的是在截断表格之后运行shrink db命令。 这是正常的程序吗？我们需要注意或了解的任何事情，或者有更好的替代方案吗？

- Paulin Shah

Answer 1

"删除所有行"... 那不是用DROP TABLE (然后重新创建一个具有相同模式/索引的空表) 更可取吗？（我个人喜欢"新的开始"）

话虽如此，TRUNCATE TABLE 也很好，如果你想要回收空间，确实需要使用 DBCC SHRINKFILE。

- mjv

Answer 2

truncate 是你要找的。如果之后需要缩小数据库，请运行收缩操作。

这个 MSDN 参考（如果你在谈论 T-SQL）比较了删除行与截断的幕后情况。

- ProKiner

---

正如其他评论所指出的那样，无论你选择哪种方法，都必须处理外键约束（如果有）。我的偏好是禁用约束，truncate表格，重新启用约束，然后dbcc shirinkfile（给自己一些时间）。 - ProKiner

Answer 3

正如所指出的，如果您无法使用truncate或drop。

SELECT 1
WHILE @@ROWCOUNT <> 0
    DELETE TOP (100000) MyTable

- gbn

---

他可以使用truncate或drop，但是任何操作（删除、drop、truncate）都可能需要注意的问题必须要处理好。 - Chad

Answer 4

使用 Truncate Table（以及 drop table）时需要记住的一件事是，如果您曾经有外键引用该表，那么这将不再起作用。

- Irwin M. Fletcher

---

在 SQL Server 上，如果存在外键约束，则无法使用 drop table。http://msdn.microsoft.com/zh-cn/library/ms173790.aspx - ProKiner

@prokiner 无论如何，你都必须处理外键引用，即无论行是否被删除、缩小或丢弃，其他表中引用这些行的任何记录都必须首先被删除或移除约束。在某些情况下，可以通过ON DELETE触发器“自动”完成此操作，但对于超过1.5亿类型的数据库来说，这几乎不适用。 - mjv

@prokiner，我应该更清楚地表达。那些是两个独立的想法，我只是指出问题是因为OP已经声明他们已经测试了截断方法。我已经整理了我的答案。 - Irwin M. Fletcher

@Irwin - 抱歉我没有表达得更清楚。你说得对，这两种方法都需要处理外键约束。我的评论只是涉及“drop”选项。无论哪种情况，操作者都需要做更多的工作，而不仅仅是一个简单的“truncate table; dbcc shrinkfile”。 - ProKiner

@mjv - 你说得对。还有一点需要记住的是，truncate table 不会触发触发器，但 delete 会触发。 - ProKiner

Answer 5

根据完整数据库的大小，缩小可能需要一段时间；我发现如果将其分成较小的块进行缩小，而不是尝试一次性全部恢复，可以加快速度。

- SqlACID

Answer 6

您有一个正常的解决方案（截断+收缩数据库）来从表中删除所有记录。

正如Irwin所指出的那样。TRUNCATE命令在被外键约束引用时无法工作。因此，首先删除约束，截断表并重新创建约束。

如果您关心性能 并且 这是您系统的常规例程。您可能需要考虑将此表移动到自己的数据文件中，然后只针对目标数据文件运行收缩操作！

- Chad