SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
当前回答
DELETE语句可以有一个WHERE子句来删除特定的记录,而TRUNCATE语句不需要任何子句并擦除整个表。 重要的是,DELETE语句记录删除日期,而TRUNCATE语句不记录删除日期。
其他回答
并不是说截断不记录SQL Server中的任何内容。truncate不记录任何信息,但它记录您触发truncate的表的数据页的释放。
如果在开始时定义事务,则可以回滚被截断的记录,回滚后可以恢复被截断的记录。但不能在提交截断事务后从事务日志备份中恢复截断记录。
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
这里有一个非常好的与主题相关的链接。
SQL server中删除与截断的总结 完整文章请点击这个链接:http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/
摘自dotnet mob文章:删除Vs截断SQL Server
Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.
参考资料请点击这里
这两个操作的另一个区别是,如果表包含一个标识列,则在TRUNCATE下该列的计数器将重置1(或为该列定义的种子值)。DELETE没有这种影响。