SQL TRUNCATE Statement - Complete Beginner Tutorial

What is the TRUNCATE Statement?

TRUNCATE is a Data Definition Language (DDL) command used to delete all rows from a table.

It works by deallocating the data pages used by the table, which makes it much faster than deleting rows one by one.

Because it does not log individual row deletions, it uses fewer system resources.

• Removes all rows from a table instantly.
• Resets identity columns to their seed values.
• Cannot be used with a WHERE clause.
• May not fire DELETE triggers.

Syntax of TRUNCATE

The basic syntax of the TRUNCATE statement is straightforward.

You specify the table name from which you want to remove all data.

TRUNCATE vs DELETE

Both TRUNCATE and DELETE remove data from tables, but they behave differently.

Understanding these differences helps you choose the right command for your needs.

• DELETE removes rows one at a time and logs each deletion; TRUNCATE deallocates data pages.
• DELETE can use WHERE clauses to remove specific rows; TRUNCATE removes all rows.
• TRUNCATE resets identity columns; DELETE does not.
• DELETE can activate triggers; TRUNCATE usually does not.
• TRUNCATE is faster and uses fewer system resources.

Effects and Limitations

While TRUNCATE is efficient, it has some important effects and limitations to consider.

It cannot be used if the table is referenced by a FOREIGN KEY constraint.

In some database systems, TRUNCATE cannot be rolled back once committed.

• Cannot truncate tables referenced by FOREIGN KEY constraints without disabling them.
• Resets identity columns to their starting value.
• May not be transactional in all database systems.
• Does not activate DELETE triggers.
• Cannot specify conditions; it removes all rows.

Example Usage of TRUNCATE

Here is a simple example demonstrating how to use TRUNCATE to remove all data from a table.