Difference Between DELETE and TRUNCATE

Difference Between DELETE and TRUNCATE

5 mins read187 Views Comment
Updated on Jun 15, 2023 09:44 IST

You will be asked in most of the interviews about the difference between DELETE and TRUNCATE. So let’s understand what exactly are the differences with their use cases.


When working with relational databases, it’s essential to know the difference between “delete” and “truncate.” Both commands can be used to remove data from a table, but they work differently and have different use cases. This article will explore the differences between delete and truncate and when to use each command.

Table of contents

Difference between DELETE and TRUNCATE

Operation Deletes specific rows from a table based on a specified condition using the WHERE clause. Deletes all rows from a table.
Rollback Can be rolled back using the transaction log or a backup. Cannot be rolled back.
Identity Does not reset the identity of the table’s primary key or any associated indexes. Resets the identity of the table’s primary key and any associated indexes.
Triggers Triggers any associated triggers and constraints on the table. Does not trigger any associated triggers or constraints on the table.
Speed Slower than TRUNCATE as it deletes one row at a time. Faster than DELETE as it deallocates all data pages at once.
Resources Consumes more transaction log space and generates more locks and blocking. Consumes less transaction log space and generates fewer locks and blocking.
WHERE Includes a WHERE clause to specify the condition for which rows to delete. Does not support a WHERE clause.
What are Different Types of Databases?
What are Different Types of Databases?
Databases have diverse types: RDBMS (structured with SQL), NoSQL (flexible for unstructured data), Hierarchical (tree-like structure), Network (complex data relationships), and OODBMS (suited for complex structures). Choice depends on data...read more
MongoDB Vs. MySQL: Applications and Differences
MongoDB Vs. MySQL: Applications and Differences
MongoDB is a NoSQL database that uses a document data model, which allows for more flexible and scalable data organization.MySQL is a relational database that uses a table data model....read more
MariaDB vs MySQL – What’s the Difference?
MariaDB vs MySQL – What’s the Difference?
Maria DB and MySQL are open-source relational database management systems (RDBMS). And because of this, many of us get confused between MariaDB vs MySQL. So, in this article, we will...read more

Must Check: SQL Online Course and Certification

What is DELETE command in SQL?

The DELETE command removes one or more rows from a table. 

DELETE is a DML (Data Manipulation Language) command that deletes rows/tuples from a specified table or relation in SQL. You can delete any number of rows using the DELETE command. It is mostly combined with a WHERE clause to delete specific rows that match a condition. If you don’t add a WHERE clause to your query, the DELETE command deletes all rows and empties the table. You must have delete permission on the table to use this command. When using the DELETE command, you must specify which rows you want to delete using a WHERE clause. If you omit the WHERE clause, the command will delete all rows in the table, which is rarely what you want to do.

Syntax and example of DELETE

Syntax: DELETE FROM table_name

WHERE condition;

Example: DELETE FROM customers WHERE id = 1;

In this example, the DELETE statement will remove all rows from the customers’ table where the id column equals 1.

Also explore:Top MySQL Interview Questions and Answers by Shiksha Online

Also explore: What is the Difference Between SQL and MySQL?

Use Cases of DELETE

  1. Removing unwanted or duplicate data: Sometimes the table is no longer needed, or duplicate lows need removal. That can be removed using a delete statement.
  2. Implementing data retention policies: you might need to delete old employee data after a certain period has elapsed.
  3. Cleaning up after testing or development: You may create temporary data in a table that needs to be removed when you’re done during testing or development. The DELETE statement can be used to remove this data.
  4. Maintaining data integrity: If you have foreign key constraints between tables, you may need to delete rows from one table when corresponding rows are deleted from another. The DELETE statement can be used to implement this behaviour.
  5. Undoing mistakes: If you make a mistake while inserting or updating data, you can use the DELETE statement to remove the incorrect data and start over.

What is TRUNCATE command in SQL ?

The TRUNCATE command is used to remove all rows from a table.

SQL Truncate is a DDL (Data Definition Language) command. Delete all rows in the table. SQL Server stores table data in pages. The truncate command deallocates pages and deletes rows. Make an entry in the transaction log to free the page. Not all row deletions are recorded in the transaction log. Unlike the DELETE command, the TRUNCATE command does not use a WHERE clause, removing all data from the table in one operation. When you use the TRUNCATE command, the table structure is preserved, but all data is lost.

Syntax and example of TRUNCATE

Syntax: TRUNCATE TABLE table_name;

truncate query in SQL

Example: TRUNCATE TABLE customers;

In this example, the TRUNCATE statement will delete all rows from the customer’s table, leaving an empty table with the same columns and primary key.

Use Case of TRUNCATE

  1. Resetting Data: In the Case of table resetting, you need to remove the data and then use the truncate command. For example, you might want to clear the contents of a log table periodically to free up space
  2. Removing Large Data Sets: In Case a large data set is no longer needed, you can quickly use the truncate command to quickly and efficiently remove it from your database. 
  3. Removing Test Data: While testing the application in a test environment, you can remove all the test data using a truncate command. This ensures that you start with a clean slate each time you run a test.
  4. Resetting auto-increment counters: While using an auto-increment column in a table to generate primary keys, truncating the table resets the counter for that column to the initial value.


Now that you know the main difference between the truncate and delete commands in SQL queries, remember that TRUNCATE is a DDL command. On the other hand, the DELETE command is a DML command. This distinction between delete vs truncate helps us assess these commands’ usage and broader meaning. Therefore, although you can use the DELETE command to remove one or more rows from a table, you may need to reset the table using the TRUNCATE command in certain circumstances. If you liked the article, please do like and share it.

Download this article as PDF to read offline

Download as PDF
About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio


We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.