SQL > Data Definition Language (DDL) > Truncate Table Statement

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,

TRUNCATE TABLE "table_name";

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

TRUNCATE TABLE Customer;

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:

TRUNCATE TABLE Customer;

and

DELETE FROM Customer;

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.

Next: SQL USE




Copyright © 2014 1keydata.com   All Rights Reserved.   Privacy Policy