Explain the similarities and differences between drop, delete, and truncate in SQL,

Explain the similarities and differences between drop, delete, and truncate in SQL,

First: Similarities:

Truncate, delete without where clause, and drop both delete table data.

Second: Differences:

1. Truncate and delete: only delete 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 the status changes to invalid.

2. The delete statement is a database operation language (dml ),This operation will be placed in the rollback segement and take effect after the transaction is committed. If a trigger is executed, it will be triggered.
Truncate and drop are database definition languages (ddl). The operation takes effect immediately.The original data is not stored in the rollback segment, and cannot be rolled back. trigger is not triggered.

3. The delete statement does not affect the extent used by the table, and the high watermark 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 minextents extent, unless the reuse storage is used; truncate will reset 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.

6. Usage:To delete some data rows, use delete. Note that the where clause must be large enough.

To delete a table, use drop.
If you want to retain the table and delete all the data, use truncate if it is irrelevant to the transaction. 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.

The article is very short. Do you have any differences between drop, delete, and truncate?

I will share with you some details:

(1) The DELETE statement deletes a row from the table each time, and saves the DELETE operation of the row as a transaction record in the log for rollback. Truncate table deletes all data from the TABLE at a time and does not store the delete operation records in logs. Deleting rows cannot be recovered. In addition, table-related deletion triggers are not activated during the deletion process. Fast execution speed.

(2) space occupied by tables and indexes. When a table is TRUNCATE, the space occupied by the table and index will be restored to the initial size, and the DELETE operation will not reduce the space occupied by the table or index. The drop statement releases all the space occupied by the table.

(3) In general,Drop> truncate> delete

(4) application scope.TRUNCATE can only be set to TABLE. DELETE can be set to table or view.

(5) TRUNCATE and DELETE only DELETE data, while DROP deletes the entire table (structure and data ).

(6) truncate and delete without where: delete data only, but not the table structure (Definition) drop statement will delete the constraints (constrain) on which the table structure is dependent ), trigger index; the stored procedure/function dependent on the table will be retained, but its status will change to: invalid.

(7) When the delete statement is DML (data maintain Language), this operation will be placed in the rollback segment and take effect after the transaction is committed. If there is a corresponding tigger, it will be triggered during execution.

(8) truncate and drop are DLL (data define language). The operation takes effect immediately. The original data is not stored in rollback segment and cannot be rolled back.

(9) exercise caution when using drop and truncate without backup. To delete some data rows, use delete and use where to restrict the impact scope. The rollback segment must be large enough. To delete a table, use drop. If you want to retain the table and delete the table data, use truncate if it is unrelated to transactions. If it is related to the transaction or the instructor wants to trigger the trigger, delete is used.

(10) Truncate table names are 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 uses less system and transaction log resources. The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

(11) truncate table deletes all rows in the TABLE, but the TABLE structure and its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

(12) 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 above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• Detailed description of the differences between DROP, TRUNCATE, and DELETE in MySQL. mysql starts from scratch.
• SQL Server Log Recovery Method (deal with drop and truncate)
• Comparison between Delete and Truncate statements for MySQL DATA Deletion
• Example of two ways for t-SQL to clear table data (truncate and delete)
• Usage of drop, truncate, and delete statements in sqlserver