Difference between truncate and delete in SQL

The truncate table name and the delete table name without the WHERE clause have the same function. All rows in the table are deleted, but the truncate table is faster than the delete table, and the truncate table uses less system and transaction log resources.

Each row deleted by the delete statement records one row in the transaction log. The truncate table deletes the data by releasing the data page used to store the table data, and only the release of the record pages in the transaction log.

For tables referenced by the foreign key constraint, the truncate table cannot be used, but the delete statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

The truncate table cannot be used in the index view.

Similarities and differences between truncate, delete, and drop:
Note: The delete statement is a delete statement without the WHERE clause.

Similarities: truncate, delete without WHERE clause, and drop both delete table data.

Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.

2. The delete statement is DML, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.
Truncate, drop is DDL, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. Trigger is not triggered.

3. The delete statement does not affect the extent used by the table. The high w2atermark (high w2atermark) keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
By default, the truncate statement releases the space to the extent of minextents, unless reuse storage is used; truncate resets the high water line (back to the beginning ).

4. Speed. Generally, drop> truncate> Delete

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.

Usage:
To delete some data rows, use Delete. Note that the WHERE clause should be included. The rollback segment should be large enough.

To delete a table, use drop

Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use Delete.