SQL Server removes the difference between data truncate and delete in a table (reproduced from the. NET Learning Network)

As we all know, TRUNCATE table can be used to delete the contents of the entire table, which is not the same as when the delete is behind the where condition. But beyond that, do we know that there are other differences between them? In this chapter we will discuss the differences between truncate and delete.

Let's look at the description of TRUNCATE TABLE first:
Deletes all rows in the table without logging a single row delete operation. TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause; However, TRUNCATE table is faster and uses fewer system and transaction log resources.

Syntax for TRUNCATE TABLE:

TRUNCATE TABLE name of the data to delete

Difference:
After 1,truncate deletes the data, the identity column in the table is reset to the defined seed value, that is, the identity column starts counting again, and the 
Deletes data with delete, the identity counter is retained, and the new self-increment counts from the maximum value before deletion.

2, deleting data using truncate will not trigger the trigger, and delete will trigger the DELETE trigger. The transaction log space that is used by the

3,truncate is very small because truncate does not record the deletion process, and deleting deletes a row of data, one data is recorded in the transaction log, and the data can be recovered later. In other words, data deleted using truncate is not recoverable, but is much faster than delete. Data deleted through delete can be recovered by backing up the file. The

4,truncate is a DDL language that is implicitly committed, so the truncate command cannot be undone by the rollback command.

above four points is the more important part, know the above four points, also probably can clearly understand when to use truncate, when to use the delete.

The following is a comparison of the abstract differences:
5,truncate uses fewer locks because the table and page are locked when you use truncate table, and delete is the locking of all rows in the table for deletion.

6, using Delete to delete all data, the table will still leave an empty page, that is, the data before the deletion is occupied by the space is not released (currently can be released through other commands), and the use of truncate table will not leave any page. The

7,truncate will reset the index of the table, so the table with the truncate operation will have a faster index than the table after the delete operation.

Note: Although truncate table can take advantage of the speed at which data is deleted, it is not possible to use truncate in all cases, and truncate will fail if the following conditions exist.
1, the table to delete data contains the Foreign key constraint, and delete can delete the associated data by cascading the delete.
2, a table that is referenced by an indexed view, and cannot use truncate.

Above is the difference between the truncate table and delete that I have sorted out, there are some wrong or missing parts please point out and discuss together.

SQL Server removes the difference between data truncate and delete in a table (reproduced from the. NET Learning Network)