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

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


当前回答

并不是说截断不记录SQL Server中的任何内容。truncate不记录任何信息,但它记录您触发truncate的表的数据页的释放。

如果在开始时定义事务,则可以回滚被截断的记录,回滚后可以恢复被截断的记录。但不能在提交截断事务后从事务日志备份中恢复截断记录。

其他回答

它方便的一个重要原因是,当您需要刷新数百万行表中的数据,但又不想重新构建它时。“Delete *”会花费很长时间,而Truncate对性能的影响可以忽略不计。

如果将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是快的,DELETE是慢的。

尽管TRUNCATE没有问责制。

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. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE. Table level lock will be added when Truncating. 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. Row level lock will be added when deleting.

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

最大的区别是truncate是不记录日志的操作,而delete是。

简单地说,这意味着在数据库崩溃的情况下,不能通过截断恢复所操作的数据,但可以通过删除恢复。

详情请点击这里