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

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

你不能用truncate这样做。

如果将TRUNCATE包装在事务中，则可以回滚。

请参阅下面的两个参考资料并自行测试:-

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

截断与删除是SQL面试中最臭名昭著的问题之一。一定要向面试官解释清楚，否则可能会让你丢掉这份工作。问题是没有多少人意识到这一点，所以如果你告诉他们YES 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是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

这里有一个非常好的与主题相关的链接。

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

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