Truncate (SQL)

Content sourced from Wikipedia, licensed under CC BY-SA 3.0.

Truncate (SQL) — a quick guide

What TRUNCATE TABLE does
- Removes all rows from a table quickly, but leaves the table itself intact (its columns, constraints, and indexes stay).
- Triggers do not fire, and some integrity checks may be bypassed during the operation.
- It was introduced in SQL:2008 as an optional feature (F200).

TRUNCATE vs DELETE
- TRUNCATE deletes all rows without a WHERE clause, and is considered a DML operation by the SQL standard. It’s often described as logically equivalent to DELETE FROM mytable.
- However, some sources treat TRUNCATE as a DDL-like operation because it can feel like a fast, structural change.

What you should know
- If you want to remove the table definition as well (the table itself), use DROP TABLE.
- TRUNCATE is usually faster and uses less transaction log space than DELETE for large tables.
- Many databases restrict TRUNCATE if other tables reference the target table with foreign keys.
- In some databases, identity/auto-increment counters reset after TRUNCATE; in others, they don’t.
- Permissions: you need appropriate privileges to run TRUNCATE.

Example
- TRUNCATE TABLE mytable;

Use TRUNCATE when you want to quickly clear all data while keeping the table structure and constraints. Use DROP TABLE to remove the table completely.