SQL Truncate

The TRUNCATE command in SQL is used to delete all rows from a table at once, while preserving the table's structure, columns, and indexes. It acts as a safety-checked, lightning-fast alternative to clearing out a table manually.

In simple words the TRUNCATE TABLE statement is a Data Definition Language (DDL) command used to instantly delete all rows within a table.

Think of TRUNCATE as a "hard reset" button for a table's data—it completely empties the table while keeping its structure, columns, constraints, and indexes fully intact.

Syntax:

TRUNCATE TABLE table_name;

The Crucial Differences Between DELETE and TRUNCATE

While both commands remove data, they function completely differently under the hood:

Feature	DELETE	TRUNCATE
Command Category	DML (Data Manipulation Language)	DDL (Data Definition Language)
Row Filtering (WHERE)	Supported. You can delete specific records.	Not Supported. It clears the entire table.
Speed & Performance	Slower. Scans and deletes row-by-row.	Extremely Fast. Deallocates entire data pages directly.
Logging Mechanism	Logs every individual row deletion in the transaction log.	Logs only the data page deallocations, saving massive log space.
Rollback Capability	Fully supported. Safe to use inside transactions.	Flavor-dependent. Autocommits in databases like MySQL, but rollbackable in SQL Server if wrapped in a transaction.
Identity/Auto-Increment	Maintains current counter values.	Resets the identity/auto-increment value back to its seed.
Triggers	Fires ON DELETE triggers.	Does not fire any triggers.
Permissions Required	Requires DELETE permissions.	Requires ALTER (or control) permissions.
Foreign Key Constraints	Respects row constraints. Will delete if allowed.	Fails immediately if the table is referenced by a Foreign Key.

Real-World Gotchas to Keep in Mind

Truncate Cannot Bypass Active Foreign KeysIf a second table points to your target table via a foreign key constraint, running TRUNCATE will throw an immediate error—even if that second table is completely empty.
The Fix: You must either drop/disable the constraint first, or resort to using the slower DELETE command instead.
The Auto-Increment TrapIf you use DELETE on a table with an auto-incrementing ID column and then insert a new row, the sequence continues seamlessly. If you use TRUNCATE, it acts like a brand-new table.

-- TRUNCATE resets your identifiers back to square one
TRUNCATE TABLE order_logs;
-- The very next order inserted will be forced back to ID 1

When to Use Which?

Use DELETE when you need to remove specific rows (e.g., WHERE status = 'inactive'), when you must fire downstream data triggers, or when you want to be able to safely undo the operation via standard transactional rollbacks.
Use TRUNCATE when you want to wipe a giant staging or log table completely clean instantaneously without bloating your database's transaction log file.