SQL - TRUNCATE TABLE

---

---

SQL provides command to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and cumbersome process.

The SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE command is used to empty a table. This command is a sequence of DROP TABLE and CREATE TABLE statements and requires the DROP privilege.

You can also use DROP TABLE command to delete a table but it will remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data again.

Syntax

The basic syntax of a TRUNCATE TABLE command is as follows.

TRUNCATE TABLE table_name;

Example

First let's create a table CUSTOMERS which can store the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

Following SQL TRUNCATE TABLE CUSTOMER statement will remove all the records of the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

Verification

Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below −

SELECT * FROM CUSTOMERS;

Following will be the output −

Empty set (0.00 sec)

TRUNCATE vs DELETE

Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below.

TRUNCATE vs DROP

Unlike TRUNCATE that resets the table structure, DROP command completely frees the table space from the memory. They are both Data Definition Language (DDL) operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back.

However, there are still some differences exist between these two commands, which have been summarized in the following table −