SQL TRUNCATE TABLE: Syntax, Usage, and Examples

The SQL TRUNCATE TABLE command removes all rows from a table instantly, without logging each row deletion. Unlike DELETE, this operation resets the table efficiently and is typically faster for large datasets.

How to Use SQL TRUNCATE TABLE

You use the SQL TRUNCATE TABLE statement to delete all records from a table while keeping its structure and schema intact. You can repopulate the table later without needing to recreate it.

Syntax

TRUNCATE TABLE table_name;

You don’t need a WHERE clause—this command always deletes every row in the table.

Example

TRUNCATE TABLE employees;

All data in the employees table disappears, but the table remains ready for new inserts.

When to Use TRUNCATE TABLE SQL

This command comes in handy when you want to clean out a table entirely and quickly. It’s especially useful during maintenance, testing, or resetting staging environments.

1. Reset a Table Without Dropping It

Let’s say you're testing an application and want to start with a clean table after each run.

TRUNCATE TABLE logs;

You preserve the structure and constraints but wipe the data.

2. Improve Performance for Bulk Deletes

TRUNCATE TABLE SQL works faster than DELETE for large tables because it doesn’t scan and log every row.

TRUNCATE TABLE user_sessions;

If you need to clear thousands of records quickly, this is the better option.

3. Maintain Schema While Reinitializing

When you’re reloading test data or staging new content:

TRUNCATE TABLE products;
INSERT INTO products VALUES (...);

You get a fresh start without needing to rebuild the table.

Examples of SQL TRUNCATE TABLE in Practice

Example 1: Clean Up Data Without Dropping the Table

TRUNCATE TABLE orders;

Deletes all order records but keeps the table, indexes, and structure.

Example 2: Combine with Transaction (in Some Databases)

In databases that support it, you can wrap TRUNCATE in a transaction.

BEGIN;

TRUNCATE TABLE temp_import;

COMMIT;

This ensures atomic cleanup.

Example 3: Use in a Reset Script

TRUNCATE TABLE daily_logs;
TRUNCATE TABLE temp_errors;

This resets temporary tables at the end of a scheduled job or daily process.

Example 4: Truncate and Insert Fresh Data

TRUNCATE TABLE notifications;

INSERT INTO notifications (id, message)
VALUES (1, 'Welcome'), (2, 'Update available');

You clear old data and refresh the content in one go.

Example 5: Compare with DELETE

-- Deletes all rows, logs each deletion
DELETE FROM archive;

-- Deletes all rows quickly, logs minimal info
TRUNCATE TABLE archive;

If you don’t need to track changes row-by-row, TRUNCATE is faster.

Learn More About TRUNCATE TABLE SQL

TRUNCATE vs DELETE vs DROP

It’s easy to confuse SQL TRUNCATE TABLE with DELETE and DROP, but they behave very differently.

• DELETE: Removes rows but keeps them in the transaction log, can use WHERE.
• TRUNCATE: Deletes all rows, logs minimally, resets identity counters (in many systems).
• DROP: Removes the table itself, including its structure.

Identity Column Reset

In many databases, SQL TRUNCATE TABLE resets the auto-increment (identity) counter.

TRUNCATE TABLE invoices;
-- Now inserting a new row will start with ID = 1 again

If you need to preserve the current identity value, use DELETE instead.

Table Truncation in SQL Server

SQL Server supports TRUNCATE TABLE and enforces certain rules:

• You can’t truncate a table referenced by a foreign key.
• TRUNCATE resets identity columns.
• It doesn’t fire DELETE triggers, which may surprise you.

TRUNCATE TABLE event_log;

In SQL Server, this works quickly and frees up space efficiently.

Permissions Required

You need ALTER or DROP privileges to use SQL TRUNCATE TABLE. It’s a powerful command that should be limited to trusted users.

-- Example: Grant truncate permission indirectly
GRANT ALTER ON tablename TO username;

Check your DBMS documentation for permission details.

Not Logged Per Row

TRUNCATE doesn’t log each row deletion, making it faster but less reversible. You can't use ROLLBACK in some databases once you truncate a table.

TRUNCATE TABLE metrics;
-- No way to undo this unless wrapped in a transaction (and supported)

Always confirm you’re okay with losing all rows instantly.

Alternatives if You Need Granularity

Use DELETE if you need row-level filtering, triggers, or logging:

DELETE FROM inventory WHERE quantity = 0;

This gives you control over what gets deleted.

Cautions When Using SQL TRUNCATE TABLE

• You can’t target specific rows—it's all or nothing.
• Triggers tied to DELETE won't activate.
• You can't truncate a table that's referenced by another table unless you first remove the constraint.

Use it in situations where speed is critical, and you're sure you want a full wipe.

SQL TRUNCATE TABLE gives you a fast, clean way to remove all records from a table while preserving its structure. It’s ideal for clearing logs, refreshing test data, or cleaning up staging environments. Use it when you don’t need fine-grained control, logging, or triggers.