⚡ SQL TRUNCATE TABLE – Quickly Delete All Rows from a Table

Need to remove all records from a table — fast and efficiently? The TRUNCATE TABLE statement is your go-to SQL command for quickly deleting all data from a table without logging each row deletion.

📘 What is SQL TRUNCATE TABLE?

The TRUNCATE TABLE statement removes all rows from a table instantly. Unlike DELETE, which logs each deleted row, TRUNCATE works like a reset — it’s faster, uses fewer system resources, and doesn’t fire DELETE triggers.

🧾 SQL TRUNCATE TABLE Syntax

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

This removes all data from the employees table, while keeping the table structure (columns, constraints, etc.) intact.

⚠️ Key Differences: TRUNCATE vs DELETE

FeatureTRUNCATE TABLEDELETE
Removes all rows?✅ Yes✅ Yes (with or without WHERE)
Can use WHERE clause?❌ No✅ Yes
Logs individual deletions?❌ No (minimal logging)✅ Yes
Triggers activated?❌ No✅ Yes
Faster performance?✅ Very fast❌ Slower for large tables
Rollback supported?✅ Yes (in most RDBMS with transactions)✅ Yes

✅ Use Cases for TRUNCATE TABLE

  • Resetting a staging or temporary table before reloading fresh data
  • Clearing audit logs or user activity history periodically
  • Purging test data from a development environment
  • Cleaning large tables during ETL processes

💡 Things to Know

  • You cannot truncate a table referenced by a foreign key constraint (even if empty).
  • In SQL Server, TRUNCATE resets identity columns to their seed value.
  • In PostgreSQL, TRUNCATE supports CASCADE to truncate dependent tables.
  • Some databases (e.g., Oracle) require specific privileges to use TRUNCATE.

🧠 Real-World Example

Let’s say you have a table sales_temp used to store daily imported sales data for reporting. Before loading the next day’s data, you can run:

TRUNCATE TABLE sales_temp;

This clears the table instantly, without affecting its schema — keeping your ETL process smooth and efficient.

📝 Summary

  • TRUNCATE TABLE is a fast, efficient way to delete all rows from a table.
  • It keeps the table structure but clears the data.
  • Ideal for bulk cleanup tasks in development, testing, or staging environments.
  • Not suitable if you need fine-grained deletions or trigger execution.