TRUNCATE vs DELETE

TRUNCATE vs DELETE is a SQL statement in the DML Statements category. TRUNCATE is a DDL command that deallocates data pages (minimally logged, non-rollbackable in some DBs). DELETE is DML. The syntax is TRUNCATE TABLE vs DELETE FROM — key differences in performance, logging, and rollback capability.. It returns comparison result. A typical example: -- TRUNCATE: TRUNCATE TABLE temp_log; -- Fast: deallocates data pages, resets identity counter -- Cannot use WHERE (all rows removed) -- Cannot roll back in MySQL (can in PostgreSQL and SQL Server) -- Resets AUTO_INCREMENT counter -- Uses minimal transaction log -- DELETE: DELETE FROM temp_log WHERE created_at < '2025-01-01'; -- Slow: logs each row deletion -- CAN use WHERE -- Can roll back -- Does NOT reset AUTO_INCREMENT --… A close relative is INSERT INTO, which inserts new rows into a table. A close relative is UPDATE, which modifies existing rows in a table. Always use WHERE to avoid updating all rows.