Differences between drop, truncate, and delete in SQL

(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.

(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.