While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for next day’s transaction or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete, and drop.
DELETE is a DML (Data Manipulation Language) command and is used when we specify the row (tuple) that we want to remove or delete from the table or relation. The DELETE command can contain a WHERE clause. 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 TableName
WHERE condition;
TRUNCATE is a DDL (Data Definition Language) command and is used to delete all the rows or tuples from a table. Unlike the DELETE command, TRUNCATE command does not contain a WHERE clause. When we use a TRUNCATE command, we are going to lose all the data on the table and we 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.
The key difference between DELETE and TRUNCATE –
Let’s see the difference between DELETE and TRUNCATE command:
The first and most important difference between TRUNCATE and DELETE command in SQL is that truncate does not log row-level details while delete is logged. Since TRUNCATE is not logged, it’s not possible to rollback it e.g. in Oracle. However, some databases may provide rollback functionality for truncate, on the other hand, DELETE is always logged and can be rolled back.
- TRUNCATE is a DDL and it operates on the data structure level. Other examples of DDL are CREATE and ALTER.
- DELETE 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.
- TRUNCATE command is used to delete all the rows from a table.
- The DELETE command is used to delete specified rows (one or more).
- There may be WHERE clause in DELETE commands in order to filter the records.
- There may not be WHERE clause in TRUNCATE command.
- TRUNCATE command is much faster than DELETE, and due to same reason, we should not use DELETE to remove large data set, since every delete operation is logged, it may be possible that our log segment gets filled and blew up if we try to empty a large table using the DELETE command.
- 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.
- The original table data is copied to space called the ‘Rollback’ space before doing the actual data manipulation. Then the alterations are done on the actual table data space.
- The TRUNCATE command never uses a roll backspace 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 backspace and we could just either use ‘Commit’ or ‘RollBack’ to accept or cancel the changes respectively.
- In the TRUNCATE command, the data page is locked before removing the table data.
- In the DELETE command, a tuple is locked before removing it.
- TRUNCATE 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.
- DELETE 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.
- TRUNCATE command is faster than the DELETE command.
- DELETE command is slower than the TRUNCATE command.
One more difference between TRUNCATE vs. DELETE comes from the fact we discussed in the first option; it does not activate the trigger, because it does not log individual row deletion, while DELETE activates the trigger, because of row-level logging. Truncate is usually used to remove all data from tables e.g. in order to empty a particular table and we can define any filter based upon WHERE clause, but with DELETE, we can define the condition in WHERE clause for removing data from tables.
Knowing the differences between any two entities widens the knowledge on both! We have landed on the right path i.e. the web page to understand the differences, especially, between the truncate and delete commands.
Information Sources:
