Comparison of DELETE and TRUNCATE

We already know how to remove specified tuples from a relation using conditions. In this case, the DELETE operator should be your first choice. However, if you want to get rid of the whole table in a relationship, how should you proceed then? For this purpose, SQL has the TRUNCATE operator. In this topic, we are going to review the differences between these two operators in detail.

How DELETE works

DELETE is a Data Manipulation Language (DML) command. We use it to remove a tuple or several tuples from the relation, or just to eliminate a tuple. To filter the tuples you want to delete, you need to apply the WHERE clause. If you do not specify any condition, DELETE removes or eliminates the entire tuple. Take a note of the syntax of the command below:

DELETE FROM table_name WHERE [condition];

Next, consider the example below. We have a table products, and we aim to leave only those items that have a discount:

The query should look as follows and result in the products table below:

DELETE FROM products WHERE Discount='No';

The DELETE command only works on one relation or table at a time. If you want to delete a tuple from different relations, you should apply separate DELETE commands to each of them.

Although the command syntax is simple, there is a downside to using DELETE — removing a tuple from a table can ruin the referential integrity of the database. That's why it is essential to think of the instruments that will protect you from removing crucial data while designing a database.

Another drawback of the DELETE command is that it locks the row or tuple that is to be removed, and after that deletes them one by one, making the procedure more resource- and time-consuming.

How TRUNCATE works

TRUNCATE is a Data Definition Language (DDL) command. Similar to DELETE, it is used to remove tuples. But unlike the DML operator, TRUNCATE eliminates all data from a table, including tuples and attribute values. However, the table structure is kept in the database, so if needed, you can recreate the deleted tuples.

The general syntax of the command is as follows:

TRUNCATE TABLE table_name

For instance, if you want to remove the products table entirely, you should write the following query:

TRUNCATE TABLE products

After that, the SELECT * FROM products request will result in the following output:

Empty set (0.00 sec)

TRUNCATE data deleting process implies working with data pages containing table data. Unlike the DELETE operator, it locks pages, not tuples. That makes the process less resource-consuming, thus the TRUNCATE operator is more efficient in terms of speed. At the same time, as the TRUNCATE command does not process deletion line by line, no triggers could be activated during execution. You shouldn't use TRUNCATE for a table referenced by any foreign key, as after applying the operator, the data can't be restored.

DELETE VS TRUNCATE sum up

Let's summarize all the key differences between DELETE and TRUNCATE:

Conclusion

In this topic, we've covered major differences between the DELETE and TRUNCATE operators and considered the possible pros and cons of using them. DELETE is your choice if you need to customize the removal of tuples from a table. However, if you need to eliminate the entire table and don't want to leave any data in it, it is better to use TRUNCATE.