Differences in Truncate,delete,drop in SQL statements

Same point:

1.truncate and delete without a WHERE clause, and drop deletes the data in the table.

Different points:

1. Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index, and the stored procedure/function that depends on the table will remain, but become invalid state.

2. The DELETE statement is a database manipulation language (DML), which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
Truncate, drop is the database definition language (DDL), the operation takes effect immediately, the original data is not placed in the rollback segment, cannot be rolled back, the operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
The drop statement frees all the space occupied by the table.
The TRUNCATE statement defaults to the space release to minextents extent unless using reuse storage;truncate resets the high watermark (back to the beginning).

4. Speed, in general: drop> truncate > Delete

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

6. TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

Differences in Truncate,delete,drop in SQL statements