What is the main difference between DELETE and TRUNCATE commands in SQL?

The main difference between DELETE and TRUNCATE commands in SQL is that a DELETE command is used if we want to delete records from the table whereas, a TRUNCATE command is used when we want to delete all the data from the table.

What is a DELETE command in SQL?

DELETE is a DML command in SQL. In order to delete any data from the table, we need DELETE permission on that particular table. It is slower than the TRUNCATE command because it maintains the log.

What is a TRUNCATE command in SQL?

A TRUNCATE command is a DDL command in SQL. It cannot be executed with a WHERE clause. It is used to delete all the rows from a particular table. Hence, it is faster than the DELETE command.

What is the space difference between DELETE and TRUNCATE commands?

Based on the space required, the difference between DELETE and TRUNCATE commands is that the DELETE command maintains a log for each deleted row. Hence, it requires more space than TRUNCATE.

What is the difference between DELETE and TRUNCATE commands in terms of table identity?

Based on the table identity, the difference between DELETE and TRUNCATE commands is that the TRUNCATE resets the table identity. However, the DELETE command deletes the data and does not reset the table identity.

Home/
GATE CSE/
GATE CS/
Article

Difference Between DELETE and TRUNCATE Command in SQL

By BYJU'S Exam Prep

Updated on: September 25th, 2023

The difference between DELETE and TRUNCATE is that the delete statement deletes data without resetting a table’s identification, whereas the truncate command resets the identity of a specific table. Check out the other key differences between DELETE and TRUNCATE in the table given below.

Difference Between DELETE and TRUNCATE Command in SQL
DELETE	TRUNCATE
When we wish to remove some or all of the records from a table, we use the DELETE statement.	TRUNCATE is a SQL statement that deletes whole rows from a table.
DELETE is a DML command because it just changes the data in the table.	TRUNCATE is a DDL command.
It can be used with the WHERE clause.	It cannot be used with the WHERE clause.
It is slower.	It is faster.
Before deleting a table row, it locks it.	It locks the whole table.
It allows us to restore the deleted data using the COMMIT or ROLLBACK statement.	After executing this command, we cannot restore the deleted data.

Table of content

1. Difference Between DELETE and TRUNCATE in SQL
2. DELETE vs TRUNCATE in SQL

Difference Between DELETE and TRUNCATE in SQL

DELETE and TRUNCATE commands can both be used to erase table data. TRUNCATE is a DDL command, while DELETE is a DML command. TRUNCATE can be used to erase all of the table’s data while keeping the table’s integrity. This article describes the difference between the DELETE and TRUNCATE commands, which is commonly used interchangeably yet are completely different.

DELETE vs TRUNCATE in SQL

DELETE command is used for removing some or all of the records from a table. In contrast, TRUNCATE statement deletes complete rows from a table. Further, the difference between DELETE and TRUNCATE commands are given below.

DELETE:

It’s a data manipulation language (DML) command.
It deletes records from a table that is no longer needed in the database.
It specifies that we have a database backup before running this operation, as this query will not be able to restore deleted records.
Database backups enable to restore data at any time in the future.
A WHERE clause can be included in the DELETE command.
When the WHERE clause is used with the DELETE command, only those rows(tuples) that satisfy the criterion are removed or deleted; otherwise, all tuples(rows) are removed by default.

TRUNCATE:

It is a statement is a DDL (data definition language) command.
It removes all of the data from a table while leaving the structure intact.
WHERE clause cannot be utilized with this operation.
The log is not kept while doing this action, we cannot revert the erased data after running this command.
The transaction log for each destroyed data page is not recorded when using the TRUNCATE command.
The TRUNCATE command is faster than the DELETE command.
After invoking the TRUNCATE command, data cannot be reversed.

Related Topics:

Difference between DBMS and RDBMS	Difference between HTTP and HTTPS
Difference between algorithm and flowchart	Difference between JDK and JRE
Difference between Python and C++	Difference between linker and loader