The TRUNCATE TABLE
keyword is the table name that you want to remove all the data Unlike the DELETE
statement, the TRUNCATE TABLE
statement does not have WHERE clause.
TRUNCATE TABLE
is similar to the DELETE
statement. however, TRUNCATE TABLE
is faster and uses fewer system and transaction log resources.
TRUNCATE TABLE table_name;
Consider a CUSTOMERS table having the following records
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | PRAYAG | 21 | RANCHI | 2500 | | 2 | VIKASH | 23 | Delhi | 1200 | | 3 | RAKESH | 23 | CHENNAI | 2300 | | 4 | RAVI | 20 | BHOPAL | 3500 | | 5 | MUKESH | 25 | GUJRAT | 4500 | | 6 | PANKAJ | 26 | GIRIDIH | 4200 | | 7 | RAHUL | 19 | PUNJAB | 90000 | +----+----------+-----+-----------+----------+
Let's TRUNCATE the table as using TRUNCATE TABLE
:
SQL > TRUNCATE TABLE CUSTOMERS;
Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below-
SQL> SELECT * FROM CUSTOMERS; Empty set (0.00 sec)
The TRUNCATE TABLE
has the following advantages over the DELETE
statement:
The DELETE
statement removes rows one at a time and inserts an entry in the transaction log for each removed row.
On the other hand, the TRUNCATE TABLE
statement deletes the data by deallocating the data pages used to store the table data and inserts only the page deallocations in the transaction logs.