Introduction
While maintaining databases, it may happen that we don’t need the records of a table anymore and thus we want to remove them. This task can be achieved in three ways. First, delete rows one by one using the DELETE command. This way works fine if we have less number of rows. But this way becomes tedious for many rows. The second way is to drop the entire table using the DROP command and re-create it. This way sounds good. But what if that table was created a long time ago and we don’t remember its structure. The third and best way is to use the TRUNCATE table command.
Source: Meme Generator
In this article, we will discuss the TRUNCATE command in detail.
What is TRUNCATE Table Command?
TRUNCATE Table command is a Data Definition Language (DDL) operation that is used to delete all the rows from an existing table. When the TRUNCATE table command is executed, all the records of a table are deleted but the structure of the table remains unchanged. Also, the TRUNCATE command is a fast command. The TRUNCATE table command is often confused with DELETE and DROP commands, but there is a huge difference. We will discuss this difference later in this article.
Source: Meme Generator
One point to be noted is that we cannot roll back the TRUNCATE table command so it should be used wisely.
Syntax of TRUNCATE Table Command
Let’s see the syntax to TRUNCATE a table from the database-
TRUNCATE TABLE table_name; |
Let’s understand the TRUNCATE table command using an example.
Example of TRUNCATE Table Command
Consider the following table.
Table - EMPLOYEE
ID |
NAME |
AGE |
ADDRESS |
1001 |
Pallavi |
26 |
Mumbai |
1002 |
Gautam |
27 |
Dehradun |
1003 |
Rishi |
28 |
Delhi |
1004 |
Amrita |
31 |
Bhopal |
1005 |
Aishwarya |
31 |
Ahmedabad |
The following query will delete all the records from the table. Thus, the table will become empty.
TRUNCATE TABLE EMPLOYEE; |
Now the EMPLOYEE table is truncated. We can check the output using the SELECT statement.
SELECT * FROM EMPLOYEE; |
The output of the above query will be-
Empty set. |
Difference between DROP and TRUNCATE
DROP |
TRUNCATE |
DROP command is slower than the TRUNCATE command. | The TRUNCATE command is faster than DROP command. |
It deletes the records as well as the structure of the table. | It only deletes the records but not the structure of the table. |
Let’s consider an example to make this difference clearer.
Example
Let’s consider the following table.
Table - STUDENT
ROLL_NO |
NAME |
AGE |
1 |
Mitali |
18 |
2 |
Aditya |
21 |
3 |
Malini |
19 |
4 |
Anand |
21 |
5 |
Pranali |
20 |
The following query will delete all the records from the table using the DROP command.
DROP TABLE STUDENT; |
The table STUDENT is deleted. We can check this using the SELECT statement.
SELECT * FROM STUDENT; |
The output of the following query will be-
Table STUDENT doesn't exist. |
Now the following query will use the TRUNCATE command.
TRUNCATE TABLE STUDENT; |
All the records of the table STUDENT are deleted. We can check this using the SELECT statement.
SELECT * FROM STUDENT; |
The output of the following query will be-
Empty set. |
Difference between DELETE and TRUNCATE
DELETE |
TRUNCATE |
DELETE command is used to delete specified rows from the table. | The TRUNCATE command is used to delete all rows from the table. |
It can contain a WHERE clause. | It can contain a WHERE clause. |
It is slower than the TRUNCATE command. | It is faster than the DELETE command. |
It is a DML command. | It is a DDL command. |
Let’s consider an example to make this difference clearer.
Example
Let’s consider the same table STUDENT defined above.
The following query will delete the records from the table using the DELETE command.
DELETE FROM STUDENT WHERE ROLL_NO = 2; |
The record from the table STUDENT is deleted. We can check this using the SELECT statement.
SELECT * FROM STUDENT; |
The output of the following query will be-
ROLL_NO |
NAME |
AGE |
1 |
Mitali |
18 |
3 |
Malini |
19 |
4 |
Anand |
21 |
5 |
Pranali |
20 |
Now the following query will use the TRUNCATE command.
TRUNCATE TABLE STUDENT; |
All the records of table STUDENT are deleted. We can check this using the SELECT statement.
SELECT * FROM STUDENT; |
The output of the following query will be-
Empty set. |
Recommended topics, DCL Commands in SQL and Tcl Commands in SQL