Truncate Table|Truncate Table Statement in SQL - sql - sql tutorial - learn sql
- Sometimes we wish to get rid of all the data in a table.
- One way of doing this is with DROP TABLE.
- But what if we wish to simply delete the data but not the table itself? For this, we can use the TRUNCATE TABLE command.
- In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).
- The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
- It was officially introduced in the SQL:2008 standard.
- 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 Wikitechy;
- 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 Wikitechy table:
TRUNCATE TABLE Wikitechy;
- and
DELETE FROM Wikitechy;
- 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.