⚡ 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

Feature	TRUNCATE TABLE	DELETE
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.