Difference Between Truncate And Delete

June 2022 · 8 minute read

Difference Between Truncate And Delete

Data creation and manipulation form the basis of databases and we call it as DDL and DML respectively. A DDL is an abbreviation for the Data Definition Language. It could either create or modify the data structures in the databases and they could not be used to alter the data present on the tables. For example, we have commands that just create a table with the specified table attributes but it never adds any rows into the table. But a DML, the Data Manipulation Language, is capable of adding, deleting or modifying data in the tables. As a general rule, the DDL commands deals with the table structures whereas the DML commands deals with the actual data. Let it be, why do we just deviate from the topic “Difference between Truncate and Delete”? There are reasons that we spoke about the DML and the DDL. You would understand it in the fore coming discussion.

What is a Truncate command?

The purpose of a Truncate command is to delete the entire table. So when you use a Truncate command, you are going lose all the data on the table and you should be cautious in using it. Let us know how to use it.

The syntax of Truncate:

        TRUNCATE TABLE table-name;

Here, you should specify the name of the table that should be deleted on the whole. This ensures that there would be any table sitting in the memory space. Here is an example of using Truncate.

Below is the ‘employee’ table and just look at the rows of data in it.

emp-idemp-namedesignation
1011JackClerk
1012RosyAdmin
1014NancyFinance

Now, let us issue the below command on the employee table.

        TRUNCATE TABLE employee;

Here is the resultant of the above syntax and there is no data in it.

emp-idemp-namedesignation

What is a Delete Command?

The purpose of a Delete command is to remove the specified rows from the table. Here, ‘Where’ clause is used here to specify the rows that need to be deleted.  When we do not specify the rows, the command would delete all the rows in the table. Just look at the syntax of it.

        DELETE FROM employee;

The above syntax deletes all the rows from the ‘employee’ table. So the resultant table would contain no data.

DELETE FROM employee WHERE emp-id = 1011;

This statement just deletes a single row whose emp-id is 1011. So the resultant table would be as below.

emp-idemp-namedesignation
1012RosyAdmin
1014NancyFinance

Differences:

Truncate -> remove entire data from the table -> Table space is freed now.

Delete -> Copy the original table data to Roll Back space -> delete the specified data/entire table -> Table space is freed but Roll Back space is filled.

Truncate is a DDL command, the trigger is not allowed here. But a Delete is a DML command, Triggers are allowed here.

So those are the differences and let us look at in a tabular form.

S.NoDifferences inTruncateDelete
1.DDL or DML?It is a DDL and it operates on the data structure level. Other examples for DDL are CREATE and ALTER.It is a DML command and it operates on the table data. The DML stands for Data Manipulation Language. The commands such as SELECT, UPDATE, and INSERT are perfect examples for a DML. The DML stands for Data Manipulation Language.

 

2.How does it work?As soon as we issue the Truncate command, it just looks for the specified table. Then it completely removes all the data from the memory.Here, the original table data is copied to space called the ‘Roll back’ space before doing the actual data manipulation. Then the alterations are done on the actual table data space.
3.RollBackThe Truncate command never uses a roll back space and we could not get back to the original data. A Rollback space is an exclusive one and is occupied when the DML commands are issued.The Delete command uses roll back space and we could just either use ‘Commit’ or ‘RollBack’ to accept or cancel the changes respectively.
4.TriggersTruncate is a DDL command,  triggers are not allowed.Delete is a DML command, Triggers are allowed here.
5.Which is faster?It could remove all the data and there is no need to check for any matching conditions. Also, the original data is not copied to the rollback space and this saves a lot of time. These two factors make Truncate work faster than the Delete.It uses the rollback space and always the original data has to be retained on it. This is an extra burden and, in turn, takes much time than the Truncate.
6.Could we use WHERE clause?As Truncate never looks for any matching conditions and it just removes all the rows, we could not use a ‘Where’ clause here.But we could always specify the condition with the help of the ‘where’ clause in the Delete command.
7.Which occupies more space?The truncate is not going to use the rollback space and it saves that memory.It needs a backup in the form of Rollback space and hence it requires more memory space than the truncate.

Knowing the differences between any two entities widens the knowledge on both! You have landed on a right path i.e. the web page to understand the differences, especially, between the truncate and delete commands. Hope you are now clear with its differences and let us know if we had helped you understand it. You could also help us pointing out which has been left!


ncG1vJloZrCvp2OxqrLFnqmeppOar6bA1p6cp2aemsFwwMScn6ennKS0unvSqJ2tr5Gnsm7AxJyfp6ecpLS6e8OinZ%2Bdopq7pLGMm5ytr5Wau27A0a6lnJmkmnqiusNmm56klamycA%3D%3D