Home Resource Centre Difference Between DELETE and TRUNCATE Commands In SQL

Table of content:

"TRUNCATE cannot be rolled back" - Fact or Myth?
What is a DELETE command?
What is a TRUNCATE command?
DROP Table Command
DELETE or TRUNCATE: which is better?
DELETE vs TRUNCATE
Frequently Asked Questions
Summing Up

Difference Between DELETE and TRUNCATE Commands In SQL

The TRUNCATE and the DELETE statement both come under SQL query for delete operation. Read ahead to find the major uses and examples of the two commands, along with key differences between the two.

0 min read

Difference Between DELETE and TRUNCATE Commands In SQL

Structured Query Language (SQL) is a powerful, declarative language used to manage data in RDBMS (relational database management systems). SQL statements are used to perform a wide variety of tasks in RDBMS, namely creating, querying, updating and deleting data in the database.

The SQL query for delete operation includes TRUNCATE and DELETE commands.

They perform deletion operations on unwanted records or rows of a table. In the DELETE statement, the rows from the table get deleted with a condition. In simple words, one or more rows can be deleted at once. but in the TRUNCATE command, all the rows get deleted at once.

When we use the DELETE query, a log is created for every row in the transaction log. This way we can get back the records using ROLLBACK before COMMIT. Also, when using TRUNCATE, we can get the records back using ROLLBACK. The difference is that TRUNCATE only logs the deallocation of the page where data is stored.

Transaction log: It keeps a record for all the transactions and modifications done by each transaction in a database.

ROLLBACK: ROLLBACK is a SQL command that undo the transactions that is not saved/ not committed.

"TRUNCATE cannot be rolled back" - Fact or Myth?

We know the DELETE command can use rollback. But, many people have a misconception that the TRUNCATE statements cannot use rollback. It is MYTH!

Myth: The truncate command works so fast that it does not have time to log its records in the transaction log.

Fact: It only logs the deallocation of the page of stored records, so the work is faster than the DELETE command.

Meanwhile, the DELETE statement logs all the rows of the table, so, the DELETE statement takes much more time than the TRUNCATE command.

In conclusion, the TRUNCATE command can be rolled back for truncated records inside the transaction.

Page: It is the fundamental unit of data storage in SQL Server.

What is a DELETE command?

The DELETE statement is a DML command (Data Manipulation Language). It is used to delete single or multiple rows (records) from a table.
The WHERE clause in DELETE command defines a condition for deleting only the selected unwanted rows from the target table.
The DELETE query creates a log file in the transaction log. It stores the records before deleting them. So, in case, we delete one or many important rows, we can get them back using the ROLLBACK command.
Since the DELETE statement is a DML command, we have to commit the changes manually. Point to remember, the ROLLBACK must be done before COMMIT.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

CODE SNIPPET IS HERE

SU5TRVJUIElOVE8gc3R1ZGVudHMgVkFMVUVTICgxLCAnSGFycnknLCAnTScpOwpJTlNFUlQgSU5UTyBzdHVkZW50cyBWQUxVRVMgKDIsICdSb24nLCAnTScpOwpJTlNFUlQgSU5UTyBzdHVkZW50cyBWQUxVRVMgKDMsICdIZXJtaW9uZScsICdGJyk7ClNFTEVDVCAqIEZST00gc3R1ZGVudHM7

Output:

Image description: Output table created by the above commands.

DELETE FROM students WHERE id='1';
SELECT * FROM students;

After execution of DELETE statement:

Difference Between DELETE and TRUNCATE Commands In SQL

Image description: the table printed without the first row.

Explanation:

Here, the table structure contains three rows. Each row has an identity column, Name, and Gender. We deleted the first row (id=1) using the WHERE clause. So, when we displayed the table, it shows the updated table structure.

What is a TRUNCATE command?

The TRUNCATE command is a DDL command (Data Definition Language). We use this command on tables to delete all the records at once.
The TRUNCATE command does not use any condition, like a WHERE clause to define a condition. So, we should use it only when all the data in the target table is unwanted.
The TRUNCATE command uses table lock to lock the table and page for truncate operation, instead of row lock. So, it only logs the deallocation of the pages that store the records, and as a result, it takes much less time than the DELETE command.
Since the TRUNCATE command is a DDL command, the commit is automatically done here.
The TRUNCATE command resets the identity to its seed value.

Seed value: seed value in SQL refers to the internal value which the Server uses to generate the next value.

Syntax:

TRUNCATE TABLE table_name;

Example:

  CODE SNIPPET IS HERE

VFJVTkNBVEUgVEFCTEUgc3R1ZGVudHM7ClNFTEVDVCAqIEZST00gc3R1ZGVudHM7

Output:

Image description: Output table contains no records but table schema is not changed.

Explanation:

Here, TRUNCATE deleted all the rows at once without using any condition but does not affect the table structure.

The same result can be achieved by using the DELETE statement without any condition. But that would be much slower as DELETE will keep a log of each row in the transaction table.

DROP Table Command

There is a DDL command called DROP table query. This command also deletes the records from a table but unlike the above two commands, it removes the entire table schema, indices, constraints, triggers. The DROP table statement will not be discussed in this article.

DELETE or TRUNCATE: which is better?

Well, both the commands have different operations though their main task is to delete records from a table. Both the commands do not remove table schema, indices, constraints, triggers like the DROP command.

If you want to delete all records of a table: Use TRUNCATE.
If you want to delete specific records of a table: Use DELETE.

DELETE vs TRUNCATE

Topic	DELETE	TRUNCATE
Definition	DELETE is a SQL command that removes one or multiple rows from a table using conditions.	TRUNCATE is a SQL command that removes all the rows from a table without using any condition.
Language	It is a DML(Data Manipulation Language) command.	It is a DDL(Data Definition Language) command.
Commit	We have to manually COMMIT the changes in the DELETE command.	The changes are automatically COMMIT in the TRUNCATE command.
Process	It deletes rows one by one with conditions.	It deletes all the data at once.
Condition	It uses the WHERE clause as a condition.	It does not take any condition i.e. does not use WHERE clause.
Lock	It locks all the rows for deletion.	It uses table lock to lock the pages for deletion.
Log	It logs every single record in the transaction log.	It only logs the deallocation of the pages where the data is stored.
Transaction space	It uses more transaction space compared to the TRUNCATION command.	It uses less transaction space compared to the DELETE command.
Identity	It does not reset the table identity to its seed value if there is an identity column.	It resets the table identity to its seed value.
Permission	It needs delete permission.	It needs alter table permission.
Speed	It is much slower when comes to big databases.	It is faster or in other words, instant.

Frequently Asked Questions

1. TRUNCATE vs DELETE - which one should I choose?

The choice between TRUNCATE or DELETE depends on specific requirements. If you need to delete all rows from a table without keeping track and speed is of the essence, then use TRUNCATE. If you need to delete specific rows from a table but need to keep track of the deleted rows, then use DELETE.

2. Which is better, TRUNCATE or DELETE?

Both TRUNCATE and DELETE serve specific functions in SQL and are equally useful. Thus, the answer to which is better among the two will depend on the specific requirements.

If you're using a transaction-based database, and need to keep track of the deleted rows, use DELETE. It's slower than TRUNCATE but allows users to track deleted rows, making it easier to restore them, if required.

If you need to free up space in your database and have to all rows from a table, use TRUNCATE. TRUNCATE is faster than DELETE and does not keep track of the deleted rows, thus saving space on the database.

3. Why is TRUNCATE faster than DELETE?

The TRUNCATE command only logs the deallocation of the page of stored records, so the work is faster than the DELETE command. Unlike DELETE, TRUNCATE also does not keep track of the deleted rows.

4. What are the top differences between TRUNCATE and DELETE?

The top differences between TRUNCATE and DELETE commands in SQL are:

Row Deletion: DELETE removes specific rows from a table. TRUNCATE removes all rows from a table.
Speed: TRUNCATE is faster than DELETE.
Tracking: DELETE keeps track of the deleted rows. TRUNCATE does not keep track of the deleted rows.
Language of the command: TRUNCATE is a DDL command. DELETE is a DML command.

Summing Up...

Both the SQL commands DELETE and TRUNCATE delete records from a table. But, the difference between DELETE and TRUNCATE is that the DELETE command uses the WHERE clause to specify rows in a table for deletion operation and the TRUNCATE command does not use any clause; the row deletion is done at once.

Also remember, the TRUNCATE command can be rolled back like the DELETE command.

You might also be interested in reading:

Edited by

Shreeya Thakur

As a biotechnologist-turned-writer, I love turning complex ideas into meaningful stories that inform and inspire. Outside of writing, I enjoy cooking, reading, and travelling, each giving me fresh perspectives and inspiration for my work.

Tags:

Computer Science