Truncate Table Command

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.

SELECTFROM 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.

SELECTFROM 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.

SELECTFROM 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.

SELECTFROM 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.

SELECTFROM STUDENT;

 

The output of the following query will be-

Empty set.

 

Frequently asked questions

  1. What is the use of the TRUNCATE table command?
    Ans. TRUNCATE table command removes all the rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. So, this command is used in those cases where we want to preserve the table structure but do not want the data.
     
  2. Can we roll back the TRUNCATE table command?
    Ans. No, we cannot roll back the TRUNCATE command. So this command should be used wisely. 
     
  3. What is the difference between DROP and TRUNCATE command?
    Ans. DROP command is used to delete the whole table along with its structure, columns, constraints and indexes while TRUNCATE is used only to delete the records. Rest all things remain the same.
     
  4. What is the difference between the DELETE and TRUNCATE commands?
    Ans. DELETE command is used to delete some rows from the table. It is mostly used with the WHERE clause whereas TRUNCATE is used to delete all the records of the table.

Key Takeaways

In this article, we have discussed the TRUNCATE Table command along with its syntax and examples. We also briefly discussed the difference between DROP and TRUNCATE command and between DELETE and TRUNCATE command along with examples.

Reader, don’t stop here. Start your DBMS journey with the DBMS course. Solve SQL problems here.

Happy Learning!

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think