The difference between truncate,delete,drop in SQL

Different points:
1. Truncate and delete Delete only the data do not delete the structure of the table (definition)
The DROP statement deletes the table's structure-dependent constraints (constrain), triggers (trigger), indexes (index); Stored procedures/functions that depend on the table are preserved, but become invalid states.

The code is as follows	Copy Code
Truncate is a statement in SQL that deletes the contents of a datasheet, using the following: Syntax TRUNCATE TABLE name Parameter name

is the name of the table that you want to truncate or the name of the table for which you want to delete all rows

Truncate table name is fast and efficient because: 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 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and if the corresponding trigger is present, the execution is triggered.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger.

DELETE statement
The DELETE statement is used to delete rows from the table.

Grammar

The code is as follows	Copy Code
DELETE from table name WHERE column name = value "Fred Wilson" will be removed: DELETE from person WHERE LastName = ' Wilson '

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

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

The 3.delete statement does not affect the extent that the table occupies
The drop statement frees up all of the space occupied by the table
Truncate statement by default, space is released to minextents extent unless you use reuse storage.
4. Speed, generally: drop> truncate > Delete

Sometimes we decide that we need to clear a table from the database. In fact, if we can't do that, that would be a big problem because the database administrator, DBA, is bound to be unable to manage the database efficiently. Fortunately, SQL has the syntax to provide a DROP table to let us clear the table. The syntax for the DROP TABLE is:

The code is as follows	Copy Code
DROP table "Table name"

If we were to clear the customer form created in SQL Create, we would enter:

The code is as follows	Copy Code
DROP TABLE customer.

5. Security: Be careful with drop and truncate, especially when there is no backup. Otherwise, it's too late to cry.
Use, to delete some data rows with delete, note the WHERE clause is taken. The rollback segment should be large enough.
To delete a table with drop
You want to keep the table and delete all the data. If it has nothing to do with the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, or delete.
If you are defragmenting the inside of your table, you can use truncate to keep up with reuse stroage and then re-import/insert the data.

In summary, the use of the above three commands is best replaced by Delete, the other two are very dangerous work, accidentally your database or table may be deleted or cleared OH.