comparison of truncate vs delete in mysql/sqlserver
The DELETE
command is used to remove rows from a table
TRUNCATE
removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE
is faster and doesn't use as much undo space as a DELETE
.
Difference
The most important difference is DELETE operations are transaction-safe and logged, which means DELETE can be rolled back. TRUNCATE cannot be done inside a transaction and can’t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.
DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.
FASTER
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
Where to USE
truncate
when table set to empty, and need reset auto-incrementing keys to 1. It's faster than DELETE because it deletes all data. DELETE will scan the table to generate a count of rows that were affected.
delete
need rows to delete based on an optional WHERE clause. need logs and apply foreign key constraints
DELETE
- DELETE is a DML Command.
- DELETE statement is executed using a row lock, each row in the table is locked for deletion.
- We can specify filters in where clause
- It deletes specified data if where condition exists.
- Delete activates a trigger because the operation are logged individually.
- Slower than truncate because, it keeps logs.
- Rollback is possible.
TRUNCATE
- TRUNCATE is a DDL command.
- TRUNCATE TABLE always locks the table and page but not each row.
- Cannot use Where Condition.
- It Removes all the data.
- TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
- Faster in performance wise, because it doesn't keep any logs.
- Rollback is possible.
- DELETE and TRUNCATE both can be rolled back when used with TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
- if there is a PK with auto increment, truncate will reset the counter
http://beginner-sql-tutorial.com/sql-delete-statement.htm
Difference:
Truncate
deletes the complete data from the table and next auto increment id will start with 1 whereasDelete
will start with next id.- Both will keep structure intact and delete data only.
- with
Delete
you can use limit whereas withTruncate
you can't.