How to delete full table data in massive quantities-difference between truncate table and delete and drop

I used to delete massive data in the entire table in SQL Server (million data records) and deleted it with delete for a long time. When I was seen by my predecessors, I was so stupid: "Use truncate table ".
Sorry, I was too easy to learn. I went back to check the information and found that truncate table is a powerful tool to quickly delete the entire table. Now I want to explain its advantages and disadvantages:
Functionally, The truncate table is the same as the delete statement without the WHERE clause, but its running speed is determined by the lever (), which is much faster than the delete statement.

I. Advantages of truncate table: the advantages of truncate table compared with Delete are as follows,

1.The transaction log space is small.. The delete statement deletes a row at a time and records an entry for each row in the transaction log. Truncate table deletes data by releasing the data page used to store table data, and only records the page release in transaction logs. This is also the reason why truncate table is faster than Delete to delete a full table.
2.Usually few locks are used.. When the delete statement is executed using the row lock, the rows in the table are locked for deletion. Truncate table always locks tables and pages, rather than locking rows.
3.No pages left in the table.After the delete statement is executed, the table still contains blank pages.. For example, you must use at least one exclusive (lck_m_x) Table lock to release empty tables in the heap. If the table lock is not used during the delete operation, the table (HEAP) contains many blank pages. For indexes, the delete operation leaves some blank pages, although these pages are quickly released through the background clearing process.

Ii. Use of truncate table
Can we use truncate table instead of delete without where? It is not possible in the following situations:
1. the truncate table cannot be used when the ID is to be retained, because the truncate table resets the ID (such as the self-increment type field in the table ). truncate table deletes all rows in the table, but the table structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use Delete instead.To delete table definitions and data, use the drop TABLE statement..
2. The truncate table cannot be used when a trigger is required,It does not trigger.
3. The truncate table cannot be used for tables referenced by the foreign key constraint (that is, the table where the primary key is located, not the table where the foreign key is located.
4.The truncate table cannot be used for tables that participate in the index view.Note that the index view is not a common view.

It seems that although truncate is easy to use, there are some restrictions. When using this method to operate a table, first determine whether the object table has an identifier, trigger, and whether it participates in the index view. xuanjicang is easy to understand ~