What's the difference between delete and truncate?

What's the difference between delete and truncate?

The way to delete data in a table is Delete,truncate,

They all delete the data in the table, not the table structure, delete the data from the entire table or delete the data from one or N of the table that satisfies the condition, and truncate can only delete the data from the entire table. And the truncate operation is called truncating the table.

Truncate action vs. delete operation

Operation

Rolling back

High watermark

Space

Efficiency

Truncate

No

Fall

Recovery

Fast

Delete

OK

Not change

Do not recycle

Slow

Truncate and delete have the following differences

1, truncate on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.

2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command.

3, TRUNCATE will reset the high-level line and all indexes. When you fully browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.

4, TRUNCATE cannot trigger any delete trigger.

5, can not give anyone the right to empty other people's table.

6. When the table is emptied, the table and table indexes are reset to their initial size, and delete cannot.

7, can not empty the parent table.