SQL中TRUNCATE和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. 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 TABLE语句,您将指示SQL Server删除表中的每条记录,而不进行任何日志记录或事务处理。

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

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

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

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

详情请点击这里

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)              |
+----------------------------------------+----------------------------------------------+

删除

DELETE是一个DML命令 DELETE可以回退 Delete =仅删除-因此可以回滚 在DELETE中,可以使用WHERE子句编写条件 语法- Delete from [Table] where [Condition]

截断

TRUNCATE是一个DDL命令 不能在TRUNCATE中回滚,TRUNCATE将永久删除该记录 Truncate = Delete+Commit -这样我们就不能回滚 不能在TRUNCATE中使用条件(WHERE子句) 语法- Truncate table [table]

详情请浏览

http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/