What is the difference between DELETE and TRUNCATE?

In SQL, both the DELETE and TRUNCATE commands are used to remove data from tables. However, they operate very differently in terms of how they remove data, their performance, logging, and transactional behavior.

Overview

DELETE is a DML (Data Manipulation Language) command that removes rows one by one, allowing for conditional deletion and transaction logging. TRUNCATE is a DDL (Data Definition Language) command that deallocates data pages, making it faster for removing all rows from a table.

DELETE Statement

The DELETE statement is used to remove one or more rows from a table. It can include a WHERE clause to specify which rows to delete. If no WHERE clause is provided, all rows are deleted. DELETE operations are logged, allowing them to be rolled back and triggering ON DELETE triggers.

DELETE FROM Employees WHERE DepartmentID = 10;
DELETE FROM Products;

• DML (Data Manipulation Language) command.
• Removes rows one by one.
• Allows WHERE clause for conditional deletion.
• Generates rollback segments (can be rolled back).
• Fires ON DELETE triggers.
• Resets AUTO_INCREMENT or IDENTITY columns only if all rows are deleted and the table is empty.
• Slower for large tables compared to TRUNCATE.
• Requires DELETE privilege.

TRUNCATE Statement

The TRUNCATE statement is used to remove all rows from a table quickly and efficiently. It works by deallocating the data pages used by the table and logging only the deallocation of pages, rather than individual row deletions. This makes it much faster than DELETE for large tables, but it cannot be rolled back and does not fire triggers.

TRUNCATE TABLE Employees;

• DDL (Data Definition Language) command.
• Removes all rows by deallocating data pages.
• Does not allow WHERE clause.
• Cannot be rolled back (implicit COMMIT).
• Does not fire ON DELETE triggers.
• Always resets AUTO_INCREMENT or IDENTITY columns.
• Faster for large tables.
• Requires DROP privilege on the table.

Key Differences