SQL Truncate Table With Examples

aimtocode

SQL Database: Definition

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.

Syntax:

  							   
  TRUNCATE TABLE  table_name;

Example: TRUNCATE TABLE

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)

TRUNCATE TABLE vs. DELETE

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.

aimtocode