批量删除(截断与删除) [英] Bulk delete (truncate vs delete)

问题描述

我们有一个包含 150 多万条记录的表.我们需要清除/删除所有行.由于它写入 t-logs 并且我们无法更改整个数据库的恢复模型，因此删除操作将永远持续下去.我们已经测试了截断表选项.

We have a table with a 150+ million records. We need to clear/delete all rows. Delete operation would take forever due to it writing to the t-logs and we cannot change our recovery model for the whole DB. We have tested the truncate table option.

我们意识到 truncate 会从表中释放页面，如果我没记错的话，可以让它们重新使用，但不会自动缩小数据库.所以，如果我们想减少数据库大小，我们真的需要在截断表后运行shrink db命令.

What we realized that truncate deallocates pages from the table, and if I am not wrong makes them available for reuse but doesn't shrink the db automatically. So, if we want to reduce the DB size, we really would need to do run the shrink db command after truncating the table.

这是正常程序吗?有什么我们需要小心或注意的，或者有什么更好的选择吗?

Is this normal procedure? Anything we need to be careful or aware about, or are there any better alternatives?

推荐答案

"Delete all rows"... 不会DROP TABLE(并重新创建一个具有相同架构/索引的空一个)更可取?(我个人喜欢重新开始";-) )

"Delete all rows"... wouldn't DROP TABLE (and re-recreate an empty one with same schema / indices) be preferable ? (I personally like "fresh starts" ;-) )

这说 TRUNCATE TABLE 也很不错，是的，如果您想恢复空间，之后可能需要 DBCC SHRINKFILE.

This said TRUNCATE TABLE is quite OK too, and yes, DBCC SHRINKFILE may be required afterwards if you wish to recover the space.

这篇关于批量删除(截断与删除)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！