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

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


当前回答

TRUNCATE是快的,DELETE是慢的。

尽管TRUNCATE没有问责制。

其他回答

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

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

详情请点击这里

微软sql server的另一个不同之处在于,使用delete,你可以使用output语句来跟踪哪些记录被删除了,例如:

delete from [SomeTable]
output deleted.Id, deleted.Name

你不能用truncate这样做。

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

SQL DELETE语句的语法是:

DELETE FROM table_name [WHERE condition];

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

truncate和delete的区别如下:

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+

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