Delete and Truncate in SQL – Key Differences & Syntax Explained

SQL is a popular programming language that provides some versatile and unique features that are not available in any other programming language. One of which is the DELETE and TRUNCATE in SQL. Almost 90% of SQL developers use it!

Both of these features are used to delete rows from a table. However, they have their differences. In this blog, we will learn about the function of these commands in SQL thoroughly. We will also look at the differences between these two commands.

DELETE and TRUNCATE: An Overview

In SQL, the DELETE and TRUNCATE commands are used to remove rows from a table. There are, however, some significant variations between them.

• Using a WHERE clause, you may define which rows to delete using DELETE. For instance, the following command would remove all entries from the Customers database whose Country field equals the USA.

DELETE FROM Customers WHERE Country = ‘USA’;

• You cannot specify which rows to remove using TRUNCATE. It just removes all of the rows from the table. The following command, for example, would remove all rows from the Customers table.

TRUNCATE TABLE Customers;

You can learn more and have a better understanding of these commands by opting for an online SQL course.

What is a DELETE Command?

The DELETE SQL statement deletes existing records from a table using Data Manipulation Language (DML). You can use this to remove certain entries based on criteria or all records from a table. It is essential to remember that the DELETE command deletes the data from the table permanently. It is not possible to retrieve erased data. As a result, while employing the DELETE command, extreme caution is required.

The DELETE command has the following syntax:

DELETE FROM table_name WHERE condition;

• table_name is the name of the table whose records you want to remove.
• condition indicates the records you wish to remove, for example, remove any entries if the age field is more than 50.

If you leave out the WHERE clause, the table’s records will be removed.

Here’s how to use the DELETE command to remove all records from the worker’s table.

DELETE FROM employees;

Given below is an example of how to use the eliminate command to eliminate all worker’s table records with ages greater than 50.

DELETE FROM employees WHERE age > 50;

Here are some pointers on how to use the DELETE command safely.

• To define which records to remove, always use a WHERE clause.
• Before using the DELETE command on the original table, test it on a replica of the table.
• Before performing the DELETE command, make a backup of the table.

What is a TRUNCATE Command?

TRUNCATE is a Data Definition Language (DDL) action in SQL that deletes all data from a table. It is a DDL command, not a DML command, and you cannot use a WHERE clause to filter the removed rows. The TRUNCATE TABLE command also does not record any transaction log entries for the erased rows, making it quicker than the DELETE statement. 

An example to use this command:

TRUNCATE TABLE customers;

This operation will delete every record from the customer’s table.

TRUNCATE is a handy command for swiftly wiping up a table. It is crucial to remember that it cannot be reversed. When you use the TRUNCATE TABLE command, the data is permanently deleted.

The following are some of the benefits and drawbacks of using the TRUNCATE TABLE command.

• It is more efficient than the DELETE statement.
• There are no entries in the transaction log.
• It cannot be reversed.
• It cannot be used with a WHERE clause to filter deleted records.
• It cannot be used to remove data from a table with a foreign key constraint.
• If the table is huge, it may cause performance issues.

Differences Between DELETE and TRUNCATE in SQL

There are certain differences between the DELETE and TRUNCATE commands:

• DELETE adds new entries to the transaction log. This implies you can undo a DELETE action, if necessary. TRUNCATE does not record modifications to the transaction log. Hence, a TRUNCATE operation cannot be rolled back.
• TRUNCATE is somewhat faster than DELETE. This is due to the fact that DELETE must examine each entry to determine whether it satisfies the criteria supplied in the WHERE clause. TRUNCATE is not required to do so because it deletes all rows regardless of their criterion.

In general, if you need to delete individual rows from a table, use DELETE, and if you need to delete all rows from a database, use TRUNCATE.

For a more detailed understanding of the DELETE and TRUNCATE differences in SQL, refer to the table given below.

Criteria	DELETE	TRUNCATE
Definition	To delete specific records from a table, you can use the DELETE command.	The TRUNCATE command is utilized to remove all data from a table.
Language Type	DML Command	DDL Command
Locks and Resources	As the DELETE command gets the lock on each deleting record, it necessitates additional locks and resources.	As it acquires the hold on the data page, the TRUNCATE command takes fewer locks and resources before destroying the data page.
WHERE Clause	It works with WHERE Clause.	It does not work with WHERE Clause.
Working	The DELETE command acts on data records and deletes records one by one in the sequence of the queries executed.	TRUNCATE acts on data pages and deletes the whole table data at once.
Speed	Its performance is sluggish because it performs operations in rows and records them in transaction logs.	It is quick since it merely records data pages in transaction logs.
Transaction Log	All deleted data rows are recorded in the transaction log.	Only deleted data pages are recorded in the transaction log.
Rollback	The COMMIT or ROLLBACK commands can be used to recover the data.	After running this command, you will be unable to recover the erased data.
Table Identity	The DELETE command deletes the records without affecting the table’s identification.	The TRUNCATE command does not remove the table structure but rather resets the table’s identity.
Indexed View	DELETE command works with an indexed view.	The TRUNCATE command does not work with an indexed view.
Triggers	It initiates the table’s triggers and causes them to fire.	It does not activate any of the table’s triggers.

Points to Remember About DELETE and TRUNCATE in SQL

The following are some points to remember when using DELETE and TRUNCATE commands:

• TRUNCATE cannot be undone, whereas DELETE can. This implies that if you unintentionally delete the wrong rows with DELETE, you may undo the changes with the ROLLBACK command. However, if you use TRUNCATE and mistakenly erase all rows, there is no way to recover them.
• TRUNCATE cannot be used with a WHERE clause, however, DELETE may be used. TRUNCATE deletes all rows from a table regardless of their criteria, whereas DELETE deletes specified rows from a table.
• DELETE is more versatile than TRUNCATE, although TRUNCATE is quicker. TRUNCATE is quicker since it does not have to verify each entry to determine whether it fits the WHERE clause requirements. DELETE, on the other hand, is more versatile since it allows you to delete individual rows from a table.

Conclusion

This blog gave you insights into DELETE and TRUNCATE in SQL. These commands are used to delete rows from the table but have their own differences in speed, working, and identity. It is important to have adequate knowledge about both of them before you use them.