Describe the difference between truncate and delete
Describe the difference between truncate and delete
DELETE |
TRUNCATE |
DROP |
|
|---|---|---|---|
| Removes | selected rows (or all) | all rows | the whole table (definition too) |
WHERE clause |
yes | no | no |
| Category | DML | DDL (Oracle, MySQL, SQL Server) | DDL |
| Triggers fire | yes (row triggers) | no DELETE triggers |
no |
| Logging | row by row | minimal (deallocates pages) | metadata |
| Speed on big tables | slow | very fast | very fast |
| Resets identity/auto-increment | no | yes (SQL Server, MySQL; PostgreSQL only with RESTART IDENTITY) |
n/a |
| Rollback | yes | yes in PostgreSQL and SQL Server (inside a transaction); not in MySQL and Oracle, which commit implicitly | MySQL/Oracle no; PostgreSQL/SQL Server yes in a transaction |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.