What is the difference between DELETE and TRUNCATE?

DELETE and TRUNCATE are SQL commands used to remove data from tables, but they work differently in terms of behavior, performance, and use cases. DELETE is a Data Manipulation Language (DML) operation that removes rows one at a time, optionally filtered by a WHERE clause. TRUNCATE, on the other hand, is a Data Definition Language (DDL) operation that removes all rows by deallocating the data pages storing the table. This fundamental difference in approach leads to variations in speed, logging, and transactional behavior.

The key distinctions lie in their execution and side effects. DELETE logs each row removal in the transaction log, which allows for granular rollbacks but slows performance on large datasets. TRUNCATE logs only the deallocation of data pages, making it faster for clearing entire tables. Additionally, DELETE activates triggers (like AFTER DELETE) for each row removed, whereas TRUNCATE does not fire triggers. TRUNCATE also resets identity columns to their seed value, while DELETE preserves the identity counter. Permissions differ too: TRUNCATE requires ALTER TABLE privileges, while DELETE needs DELETE permissions. TRUNCATE cannot be used if the table is referenced by a foreign key constraint, whereas DELETE can handle such cases with proper filtering.

Use cases depend on the scenario. For example, to remove specific rows (e.g., deleting inactive users with DELETE FROM Users WHERE status = 'inactive'), DELETE is necessary. For resetting a table entirely (e.g., clearing temporary data before a batch job), TRUNCATE is more efficient. However, TRUNCATE cannot be used in transactions in some databases (like MySQL with certain storage engines), while DELETE is fully transactional. Developers should choose based on whether they need partial data removal, trigger execution, or speed.