Difference between truncate, delete, and drop in SQL

The similarities between the three commands: truncate and delete without the where clause, and drop will delete the data in the table. The difference is that truncate will clear the table data table and set the id to start from 1, delete: only records are deleted. drop can be used to delete tables or databases and release all the space occupied by tables.

The similarities between the three commands: truncate and delete without the where clause, and drop will delete the data in the table. The difference is that truncate will clear the table data table and set the id to start from 1, delete: only records are deleted. drop can be used to delete tables or databases and release all the space occupied by tables.

Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement deletes the constrain, trigger, and index constraints on the table structure. Stored Procedures/functions dependent on the table are retained, but it changes to the invalid status.

The Code is as follows:
Truncate is a SQL statement used to delete data table content. It is used as follows: Syntax truncate table name Parameter name

Is the name of the table to be truncated or the name of the table to delete all its rows

The Truncate table name is fast and efficient, because the truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the table. However, truncate table is faster than DELETE, and less system and transaction log resources are used.

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.

DELETE statement
The DELETE statement is used to DELETE rows in a table.

Syntax

The Code is as follows:
Delete from table name WHERE column name = Value "Fred Wilson" will be deleted: Delete from Person WHERE LastName = 'wilson'

Delete all rows
You can delete all rows without deleting a table. This means that the table structure, attributes, and indexes are complete:

The Code is as follows:
Delete from table_name or: DELETE * FROM table_name

3. The delete statement does not affect the extent occupied by the table.
Drop statement to release all the space occupied by the table
The truncate statement is released to the minextents extent by default, unless reuse storage is used.
4. Speed. Generally, drop> truncate> delete

Sometimes we decide we need to clear a table from it. In fact, if we cannot do this, it will be a big problem, because the Database Administrator (DBA) is bound to be unable to efficiently manage the Database. Fortunately, SQL provides a DROP TABLE syntax to clear the TABLE. The drop table syntax is:

The Code is as follows:
Drop table "TABLE name"

To clear the customer table created in SQL CREATE, enter:

The Code is as follows:
Drop table customer.

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.
To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.
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.
If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

In summary, it is best to use delete instead of the above three commands. The other two are very dangerous tasks. Accidentally, your database or table may be deleted or cleared.