Similarities and differences in truncate, deletes, and drop in SQL Server

Same point:

Truncate and delete with no WHERE clause, and drop deletes data in the table

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), and the stored procedures/functions that depend on the table remain, but become invalid states.

2. The DELETE statement is the database manipulation language (DML), which is placed in the rollback segement, which takes effect after the transaction is committed, and if there is a corresponding trigger, the execution is triggered.

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 the trigger.

The 3.delete statement does not affect the extent that the table occupies, and the high watermark keeps the original position fixed.

Obviously the drop statement frees up all the space occupied by the table.

Truncate statement by default, space is released to minextents extent unless the reuse storage;truncate is used to reset the high watermark (back to the beginning).

4. Speed, generally: drop> truncate > Delete

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.

Want to delete the table, of course with drop

You want to keep the table and delete all the data, if it's not related to 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.