SQL中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 *”会花费很长时间,而Truncate对性能的影响可以忽略不计。

SQL server中删除与截断的总结 完整文章请点击这个链接:http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

摘自dotnet mob文章:删除Vs截断SQL Server

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

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

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

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

如果不小心使用Delete/Truncate从表中删除了所有数据。您可以回滚已提交的事务。恢复上次备份并运行事务日志,直到将要执行删除/截断操作。

以下相关信息来自一篇博客文章:

While working on database, we are using Delete and Truncate without knowing the differences between them. In this article we will discuss the difference between Delete and Truncate in Sql. Delete: Delete is a DML command. Delete statement is executed using a row lock,each row in the table is locked for deletion. We can specify filters in where clause. It deletes specified data if where condition exists. Delete activities a trigger because the operation are logged individually. Slower than Truncate because it Keeps logs Truncate Truncate is a DDL command. Truncate table always lock the table and page but not each row.As it removes all the data. Cannot use Where condition. It Removes all the data. Truncate table cannot activate a trigger because the operation does not log individual row deletions. Faster in performance wise, because it doesn't keep any logs. Note: Delete and Truncate both can be rolled back when used with Transaction. If Transaction is done, means committed then we can not rollback Truncate command, but we can still rollback Delete command from Log files, as delete write records them in Log file in case it is needed to rollback in future from log files. If you have a Foreign key constraint referring to the table you are trying to truncate, this won't work even if the referring table has no data in it. This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table. Delete table is a logged operation. So the deletion of each row gets logged in the transaction log, which makes it slow. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row instead it logs the deallocation of the data pages of the table, which makes it faster. ~ If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.

在这个例子中,Truncate也可以被rollback

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee