The difference between the delete,truncate,drop of Ms_sql! Retention

Same point:

Truncate and delete without a WHERE clause, and drop deletes the data in the table

Different points:

1. Truncate and delete only delete data without deleting the structure of the table (definition)

The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.

The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.

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.

The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.

Apparently the drop statement frees all the space occupied by the table.

The TRUNCATE statement frees space to minextents extent by default unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).

4. Speed, in general: drop>; Truncate >; Delete

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

For use, to delete some data rows with delete, note the WHERE clause. 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 is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.

If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/Insert Data

In Layman's words:

In practical applications, the difference between the three is clear.

When you no longer need the table, use drop;

When you still want to keep the table, but to delete all records, use truncate;

When you want to delete some records (always with a WHERE clause), use Delete.

A more detailed introduction:

The database Delete statements are described separately:

Delete: Used to delete rows in a table (note: You can delete a row, or you can delete all rows without deleting the table (meaning that the structure, properties, indexes of the table are complete)

Syntax: Delete a row: Delete from table name Where column name = value

Delete all rows: Delete from table name or delete * from table name

Drop: Used to delete a table (note: The structure, properties, and indexes of the table are also deleted.) ）

Syntax: Drop table name

Truncate: Used to delete data within a table (note: Delete only the data within the table, not the table itself, equivalent to the DELETE statement does not write the WHERE clause)

Syntax: Truncate table name

Comparison of the same and different points in the deleted statement in the database:

Same point:

1.truncate and delete without a WHERE clause, and drop can delete data within a table

2.truncate and delete only delete table data preserving table structure

Different points:

1. TRUNCATE TABLE TableName

Delete Table content frees the table space to preserve the table structure (that is, delete only the data within the table, not the table itself.) Equivalent to the DELETE statement does not write the WHERE clause), and does not use transactions that are not transaction-independent

The TRUNCATE statement frees space to minextents extent by default.

The high watermark is reset (back to the beginning) unless the reuse storage;truncate is used.

2. Delete Table TableName [WHERE clause]

Delete belongs to data manipulation language (DML), cannot commit transactions automatically, commit commits are required

This operation will be put into rollback segement, after the transaction is committed;

If there is a corresponding trigger, the execution time will be triggered.

The DELETE statement does not affect the extent occupied by the table, and the high waterline (hi Watermark) remains in its original position.

3. DROP TABLE TableName

Drop belongs to the data definition language (DDL) to automatically commit transactions;

The drop statement deletes the structure of the table by the dependent constraint (constrain), trigger (trigger), index (index) "Delete table data and delete table structure";

Stored procedures/functions that depend on the table are preserved, but become invalid states.

The drop statement frees all the space occupied by the table.

The speed at which data is deleted, in general: drop> truncate > Delete

Use occasions:

When you no longer need the table, use drop;

When you still want to keep the table, but to delete all records, use truncate;

When you want to delete some records (always with a WHERE clause), use Delete.

Attention:

For tables that have a primary foreign key relationship, you cannot use truncate and you should use a DELETE statement without a WHERE clause, because truncate is not recorded in the log and cannot be activated

The difference between the delete,truncate,drop of Ms_sql! Retention