SQL TRUNCATE TABLE

Sometimes we wish to get rid of all the data in a table. One way of doing this is with DROP TABLE, which we saw in the last section. But what if we wish to simply delete the data but not the table itself? For this, we can use the TRUNCATE TABLE command.

The syntax for TRUNCATE TABLE is,

So, if we wanted to truncate the Customer table that we created in SQL CREATE TABLE, we simply type,

Please note that the TRUNCATE TABLE command cannot delete any rows of data that would violate FOREIGN KEY or other constraints.

Truncate Table vs Delete

Functionally, the following two SQL statements are equivalent. Both will delete all rows from the Customer table:

and

The difference between the two is in the amount of system resources consumed. DELETE FROM requires more system resources, and hence takes longer to complete, because the RDBMS has to record all changes one row at a time in the transaction log, while a TRUNCATE TABLE operation does not record the change one row at a time, so it can be completed quicker.