Delete vs Truncate in MySQL and MS SQL Server

Delete vs Truncate in MySQL and MS SQL Server

DELETE

1. DELETE is a DML command.
2. The DELETE statement is executed using a row lock; each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if a "where" condition exists.
5. Delete activates a trigger because the operations are logged individually.
6. Slower than truncate because it keeps logs.
7. Rollback is possible.

TRUNCATE

1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use the WHERE condition.
4. It removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster performance-wise, because it doesn't keep any logs.
7. 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

Learn SQL with us at PCWorkshops!