The truncate, delete, and drop differences of SQL

Same point:
Truncate and delete without a WHERE clause, and drop will delete 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.
It is clear that 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 > Delete5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
For use, to delete some data rows with delete, note the WHERE clause. The rollback segment should be large enough.
Want to delete the table, of course with drop
To keep the table and delete all the data, if it is not related to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

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.
    
TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.
    
For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.
    
TRUNCATE table cannot be used for tables that participate in an indexed view.
In the same place:
1.truncate and delete without a WHERE clause, and drop deletes the data in the table.
2.drop, truncate are DDL statements (data definition language) that are automatically committed after execution. The difference: 1.drop and delete simply delete the table's data (definition), the drop statement will delete the table's structure, dependent constraints (constrain), triggers (trigger), indexes (index), and stored procedures/functions that depend on the table will be preserved. But becomes invalid state. The 2.delete statement is a DML language, which is placed in the rollback segement and is not effective until the object is submitted, and is triggered when the trigger (trigger) is executed. Truncate, drop is the DDL language, the operation is effective, the original data will not be placed in the rollback, can not be rolled back, the operation will not trigger trigger.
The 3.delete statement does not affect the extent that the table occupies, and the high watermark (Hi Watermark) remains in its original position. The drop statement frees all the space occupied by the table. The TRUNCATE statement releases space to the extent of minextents by default, unless reuse storage is used. Truncate will reset the high watermark (back to the original).
4. Efficiency: Drop > Truncate > Delete
5. Security: Careful use of drop and truncate, especially when there is no backup, want to delete some data can use delete need to take WHERE clause, rollback segment is big enough, want to delete table can use drop, want to keep table just want to delete all the data of the table, If you have nothing to do with things you can use truncate, if it is related to things, or want to trigger trigger, or delete, if it is to tidy up the internal fragments of the table, you can use truncate to follow reuse Stroage, and then re-import, insert data.
6.delete is a DML statement and is not automatically committed. Drop/truncate are DDL statements that are automatically committed after they are executed. Drop is typically used to delete global data such as tables, schemas, indexes, views, integrity limits, etc. delete to delete local data such as a tuple in a table drop the table structure is deleted delete just erase the data when you no longer need the table, with drop; When you still want to keep the table, But to delete all records, use truncate; When you want to delete some records (always with a WHERE clause), use Delete.

The truncate, delete, and drop differences of SQL