TRUNCATE TABLE in SQL

TRUNCATE TABLE removes all records from a table while keeping the table structure.

TRUNCATE TABLE students;

All rows from the students table are removed.

The basic syntax is:

TRUNCATE TABLE table_name;

Replace table_name with the name of the table you want to empty.

Suppose a table named students contains many records.

TRUNCATE TABLE students;

All student records are removed, but the students table remains available.

TRUNCATE removes the rows but does not remove the table itself.

TRUNCATE TABLE students;

After TRUNCATE, the table can still be used for inserting new records.

TRUNCATE does not remove selected records. It removes all records from the table.

TRUNCATE TABLE employees;

Every row in the employees table is removed.

DELETE can remove selected records using WHERE.

DELETE FROM students
WHERE city = 'Patna';

TRUNCATE removes all records.

TRUNCATE TABLE students;

TRUNCATE removes all rows but keeps the table structure.

TRUNCATE TABLE students;

DROP TABLE removes both the data and the table structure.

DROP TABLE students;

TRUNCATE does not support a WHERE clause.

This is incorrect:

TRUNCATE TABLE students
WHERE city = 'Patna';

If you want to remove selected records, use DELETE.

DELETE FROM students
WHERE city = 'Patna';

You can truncate a table even if it already contains no records.

TRUNCATE TABLE students;

The table remains available for future use.

TRUNCATE is commonly useful when you need to empty a table completely, such as a temporary or staging table.

TRUNCATE TABLE temporary_students;

The table remains available after the operation.

After TRUNCATE, the columns and their definitions remain.

TRUNCATE TABLE students;

DESCRIBE students;

The table structure can still be inspected.

In MySQL, TRUNCATE resets an AUTO_INCREMENT counter for a table.

TRUNCATE TABLE students;

Afterward, newly inserted records can start again from the initial AUTO_INCREMENT value.

TRUNCATE removes the rows but keeps the table definition, including its primary key definition.

TRUNCATE TABLE students;

The primary key structure remains part of the table.

Foreign key relationships can affect whether a table can be truncated.

TRUNCATE TABLE courses;

If other tables reference the table, the database system may restrict the operation depending on the relationship and database configuration.

TRUNCATE can be useful for clearing temporary or staging tables.

TRUNCATE TABLE temp_students;

The table remains available after all its rows are removed.

First select the database you want to work with.

USE school_db;

TRUNCATE TABLE students;

The students table in school_db is emptied.

You can specify the database name along with the table name.

TRUNCATE TABLE school_db.students;

This identifies the students table inside the school_db database.

Before truncating a table, you can check how many records it contains.

SELECT COUNT(*) AS total_students
FROM students;

This helps you confirm how much data is currently stored.

You can inspect the table before removing all its rows.

DESCRIBE students;

This shows the columns and their definitions.

TRUNCATE has transaction behavior that depends on the database system. In MySQL, TRUNCATE TABLE is treated as a DDL operation and causes an implicit commit.

TRUNCATE TABLE students;

Therefore, do not assume that a TRUNCATE operation can always be rolled back like a normal DELETE operation.

TRUNCATE removes the table's rows while keeping the table structure and associated index definitions.

TRUNCATE TABLE students;

The indexes remain part of the table structure.

TRUNCATE can be useful during development when you need to clear test data but keep the table structure.

TRUNCATE TABLE test_students;

The empty table can then be reused for new testing.

Testing systems often generate temporary data. TRUNCATE can quickly clear that data while keeping the table ready for another test.

TRUNCATE TABLE test_results;

The table remains available for the next test cycle.

Before truncating an important table, make sure the data is backed up if it may be needed later.

TRUNCATE TABLE students;

The three commands have different purposes:

• DELETE → removes selected rows and can use WHERE.
• TRUNCATE → removes all rows but keeps the table structure.
• DROP TABLE → removes the complete table.

DELETE FROM students WHERE student_id = 101;

TRUNCATE TABLE students;

DROP TABLE students;

A common mistake is using TRUNCATE when only a few records should be deleted.

TRUNCATE TABLE students;

This removes every row.

For selected records, use DELETE:

DELETE FROM students
WHERE student_id = 101;

You can use SELECT to verify that the table contains no rows.

TRUNCATE TABLE students;

SELECT *
FROM students;

The SELECT query should return no records if the table was successfully truncated.

Suppose a temporary attendance table contains old test records.

TRUNCATE TABLE test_attendance;

All test attendance records are removed while the table remains ready for new data.

Here is a simple workflow for checking and clearing a table.

SELECT COUNT(*) AS total_records
FROM test_students;

TRUNCATE TABLE test_students;

SELECT COUNT(*) AS total_records
FROM test_students;

The first query checks the records before TRUNCATE and the second checks them afterward.

Remember that TRUNCATE removes every row from the table but keeps the table itself.

TRUNCATE TABLE students;

If you need to remove only specific records, use DELETE with a WHERE condition.