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

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


当前回答

简而言之,truncate不会记录任何内容(因此更快,但不能撤消),而delete会记录日志(并且可以是更大事务的一部分,将回滚等)。如果您在开发中有不想在表中使用的数据,通常最好截断,因为您不会冒填满事务日志的风险

其他回答

简而言之,truncate不会记录任何内容(因此更快,但不能撤消),而delete会记录日志(并且可以是更大事务的一部分,将回滚等)。如果您在开发中有不想在表中使用的数据,通常最好截断,因为您不会冒填满事务日志的风险

如果不小心使用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

通过发出TRUNCATE TABLE语句,您将指示SQL Server删除表中的每条记录,而不进行任何日志记录或事务处理。

TRUNCATE是快的,DELETE是慢的。

尽管TRUNCATE没有问责制。