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

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


当前回答

TRUNCATE是DDL语句,而DELETE是DML语句。以下是两者的区别:

As TRUNCATE is a DDL (Data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other hand DELETE is a DML (Data manipulation language) statement hence requires explicit commit to make its effect permanent. TRUNCATE always removes all the rows from a table, leaving the table empty and the table structure intact whereas DELETE may remove conditionally if the where clause is used. The rows deleted by TRUNCATE TABLE statement cannot be restored and you can not specify the where clause in the TRUNCATE statement. TRUNCATE statements does not fire triggers as opposed of on delete trigger on DELETE statement

这里有一个非常好的与主题相关的链接。

其他回答

TRUNCATE是DDL语句,而DELETE是DML语句。以下是两者的区别:

As TRUNCATE is a DDL (Data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other hand DELETE is a DML (Data manipulation language) statement hence requires explicit commit to make its effect permanent. TRUNCATE always removes all the rows from a table, leaving the table empty and the table structure intact whereas DELETE may remove conditionally if the where clause is used. The rows deleted by TRUNCATE TABLE statement cannot be restored and you can not specify the where clause in the TRUNCATE statement. TRUNCATE statements does not fire triggers as opposed of on delete trigger on DELETE statement

这里有一个非常好的与主题相关的链接。

DELETE语句:该命令只根据where子句中给定的条件从表中删除行,如果没有指定条件,则从表中删除所有行。但是它不会释放包含表的空间。

SQL DELETE语句的语法是:

DELETE FROM table_name [WHERE condition];

TRUNCATE语句:该命令用于删除表中的所有行,并释放包含表的空间。

一眨眼的功夫就不能做DDL。

都是很好的答案,我必须补充一句:

由于TRUNCATE TABLE是一个DDL(数据定义语言)命令,而不是DML(数据操作语言)命令,删除触发器不会运行。

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

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