SQL中TRUNCATE和DELETE的区别是什么?

如果你的答案是针对特定平台的,请注明。


当前回答

如果将TRUNCATE包装在事务中,则可以回滚。

请参阅下面的两个参考资料并自行测试:-

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

截断与删除是SQL面试中最臭名昭著的问题之一。一定要向面试官解释清楚,否则可能会让你丢掉这份工作。问题是没有多少人意识到这一点,所以如果你告诉他们YES Truncate可以回滚,他们很可能会认为答案是错误的。

其他回答

通过发出TRUNCATE TABLE语句,您将指示SQL Server删除表中的每条记录,而不进行任何日志记录或事务处理。

这两个操作的另一个区别是,如果表包含一个标识列,则在TRUNCATE下该列的计数器将重置1(或为该列定义的种子值)。DELETE没有这种影响。

是的,DELETE比较慢,TRUNCATE比较快。为什么?

DELETE必须读取记录,检查约束,更新块,更新索引,并生成重做/撤销。所有这些都需要时间。

TRUNCATE只是在数据库中为表(High Water Mark)调整一个指针,然后!数据不见了。

这是Oracle特有的,AFAIK。

DELETE The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

来自:http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

“Truncate不会记录任何东西”是正确的。我想更进一步:

Truncate不会在事务的上下文中执行。

截断比删除的速度优势应该是明显的。根据你的情况,这种优势从微不足道到巨大不等。

然而,我看到截断无意中破坏了引用完整性,并违反了其他约束。通过在事务外部修改数据而获得的权力,必须与在没有安全网的情况下走钢丝时继承的责任相平衡。