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.

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.

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.

Differences:

• Truncate is a DDL; Delete is a DML: We have discussed above how the DDL and the DML work in our above discussion. The Truncate command is a DDL and it operates on the data structure level. But Delete is a DML command and it operates on the table data. Other examples for DDL are CREATE and ALTER. In a similar way, we could say the commands such as SELECT, UPDATE, and INSERT is perfect examples for a DML.
• How does the Truncate and Delete works: 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. But the working procedure is little different in the case of a Delete. 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. So, both of them differ in the way they work.

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.

• Roll back: A roll back is like an Undo command in our Microsoft accessories. It is used to cancel the changes that we have done recently i.e. from the last saved point. To carry out the operation, the data should be copied to the Roll Back space before it is being edited. Although these Roll backs require the extra memory, they are extremely useful to get back to the original. Especially, when you some editing by mistake! Let us come to the Truncate and Delete with respect to the Roll back now. As we discussed above, the Truncate never uses a roll back space and we could not get back to the original data. But Delete command uses roll back space and we could just either use ‘Commit’ or ‘RollBack’ to accept or cancel the changes respectively.
• Triggers: For those who need an explanation about triggers, here is a little note. Triggers are a pre-specified set of operations/operation that should be activated when the table encounters a particular condition. For example, we could trigger changing the salary amount when an employee’s experience with the company is more than a year. These triggers could work on other tables as well. For example, we could update the finance table as soon as a hike in salary has been done for an employee.

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

• Which is faster? As you guessed, the Truncate command would be faster than the Delete command. The former 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.
• Could we use WHERE clause? The ‘Where’ clause is used to specify certain matching conditions and is nothing to with the Truncate. 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.
• Which occupies more space? The truncate is not going to use the rollback space and it saves that memory. But delete needs a backup in the form of Rollback space and hence it requires more memory space than the truncate.

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

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!