intro
Let's explore the TRUNCATE vs DELETE SQL comparison and discover the key differences between the two record removal queries.
As an experienced SQL user, you know that both DELETE and TRUNCATE help you remove records from a database. You may also know that TRUNCATE removes all records, while DELETE lets you specify which rows to delete. But do you really understand all the TRUNCATE vs DELETE differences?
Read this DELETE vs TRUNCATE SQL guide and grasp how the two statements work at a low level and which one is best for your use case.
DELETE vs TRUNCATE: SQL Statement Comparison
Let's begin the DELETE vs TRUNCATE SQL comparison article with an initial head-to-head overview.
What Is the DELETE Statement?
In SQL, the DELETE statement removes specific rows from a table—generally, based on one or more filter conditions. Learn more in our article explaining the SQL DELETE statement.
For more details, refer to the official documentation guides:
DELETE Statement Syntax
Below is the basic syntax for using DELETE in standard SQL:
1
DELETE FROM table_name
2
[WHERE condition];
Where:
Note: While the WHERE clause is optional, it is usually included to prevent deleting all rows in the table. This is because if WHERE is omitted, DELETE removes all records.
How DELETE Works in SQL
When executing a DELETE statement, the database engine processes each row individually by applying the WHERE condition (if provided). Since DELETE is a DML (Data Manipulation Language) operation, it logs each deleted row in the transaction log. That means you can roll a DELETE back when wrapping it in a database transaction.
In most cases, DELETE acquires row-level locks on a table, preventing other transactions from modifying the targeted rows until the operation is complete. However, this behavior may vary depending on the specific DBMS or certain conditions.
Under the hood, DELETE also triggers any associated DELETE triggers, updates indexes, and enforces foreign key constraints. As auto-increment values remain unchanged after the operation, deleting rows usually leaves gaps in primary key sequences.
Time to continue our TRUNCATE vs DELETE comparison by diving into TRUNCATE.
What Is the TRUNCATE Statement?
In SQL, TRUNCATE, also known as TRUNCATE TABLE, is a statement to quickly remove all rows from a table.
For more information, refer to the official documentation pages:
TRUNCATE: Syntax and Usage
The basic syntax for the TRUNCATE command in SQL is:
1
TRUNCATE [TABLE] table_name;
Where:
Notes:
How TRUNCATE Works in SQL
When executing a TRUNCATE statement, the database engine removes all rows from a table by deallocating entire data pages; which are fixed-size storage blocks that contain multiple rows of table data, serving as the basic unit for storing and managing data within a database.
Since TRUNCATE is an SQL DDL (Data Definition Language) operation, it is minimally logged. That means it cannot be rolled back. Plus, unlike DELETE, it does not trigger any trigger or enforce row-by-row foreign key constraints, making it significantly faster for bulk removals.
Generally, TRUNCATE acquires a schema modification lock, preventing other transactions from accessing the table while the operation is in progress. At the end of the truncation operation, in most DBMSs (but not PostgreSQL), TRUNCATE resets auto-increment values, effectively clearing the table as if it were newly created.
TRUNCATE vs DELETE: Example List
You now have a solid understanding of what TRUNCATE and DELETE are, how to use them, and how they work. To better grasp their differences, let’s see them in action with an example.
Note 1: The queries below will be executed in DbVisualizer, a multi-database, fully-featured, top-rated database client. Still, any other database client will work just as well.
Note 2: The queries below will be written and executed in MySQL, but you can easily adapt them to PostgreSQL, SQL Server, Oracle, and other databases.
Time to let the TRUNCATE vs DELETE SQL examples begin!
Presenting the Initial Data
Suppose you have a products table in your MySQL database with the following structure:
Exploring the DDL of the products table in DbVisualizer
And containing the following data:
You can recreate this table using the SQL script below:
1
CREATE TABLE products (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
name VARCHAR(255) NOT NULL,
4
description TEXT,
5
price DECIMAL(10, 2)
6
);
7
8
INSERT INTO products (name, description, price) VALUES
9
('Wireless Mouse', 'A high-quality wireless mouse with ergonomic design.', 29.99),
10
('Bluetooth Keyboard', 'Compact Bluetooth keyboard with a slim profile.', 49.99),
11
('Laptop Stand', 'Adjustable laptop stand for better ergonomics and posture.', 19.99),
12
('Monitor Arm', 'Heavy-duty monitor arm for adjustable screen height and angle.', 89.99),
13
('Gaming Headset', 'Surround sound gaming headset with a built-in microphone.', 69.99),
14
('Game Controller', 'Wireless game controller compatible with PC and console.', 39.99),
15
('Coffee Maker', 'Automatic coffee maker with programmable settings.', 99.99),
16
('Blender', 'High-speed blender with multiple speed settings.', 79.99),
17
('Running Shoes', 'Comfortable and lightweight running shoes for all-day wear.', 59.99),
18
('Sports Watch', 'Multi-functional sports watch with heart rate monitor and GPS.', 149.99);
In the following examples, we will use this base scenario and apply TRUNCATE or DELETE queries to the products table.
Remove Some Rows
Assume your goal is to remove all products whose price is less than 40. Should you use TRUNCATE or DELETE? The answer is clear: DELETE!
That is because to achieve this goal, you must apply a filtering condition using a WHERE clause, which TRUNCATE does not support.
You can achieve the goal with the following query:
1
DELETE FROM products
2
WHERE price < 40;
Execute the query, and you will see that it removes 3 products, as expected:
Specifically, the products “Wireless Mouse,” “Laptop Stand,” and “Game Controller” have been removed:
Delete All Rows
Consider a scenario where you want to clean the products table by removing all of the rows in a table. While TRUNCATE is the operation designed specifically for that, let’s explore what happens when using DELETE.
To achieve the goal, you can simply write a DELETE query without the WHERE clause:
1
DELETE products;
The database will go through each row and delete them one by one. As you can see from the output, 10 rows have been logged as deleted:
Now, suppose products still contain all 10 products again. This time, write a DELETE query in a transaction that includes a ROLLBACK instruction at the end:
1
START TRANSACTION;
2
3
DELETE FROM products;
4
5
ROLLBACK;
Since DELETE is transactional, you expect the whole transaction to be rolled back and not see any changes in the products table:
And that is exactly what happens—even though the DELETE statement would have been executed correctly, ROLLBACK causes the transaction to be restored, and as a result, no data is deleted.
The products table remains unchanged:
Truncate All Rows
Repeat the example above, but this time using TRUNCATE:
1
TRUNCATE TABLE products;
The resulting log will show 0 rows affected:
This happens because TRUNCATE is minimally logged and does not keep track of the individual rows removed. Instead, it cleans the entire table at a low level.
Now, repeat the failing transaction experiment with TRUNCATE:
1
START TRANSACTION;
2
3
TRUNCATE TABLE products;
4
5
ROLLBACK;
The transaction executes without error:
However, after the ROLLBACK, the TRUNCATE operation cannot be undone, and the table remains empty:
Et voilà! You are now an SQL DELETE vs TRUNCATE master.
SQL DELETE vs TRUNCATE Final Comparison Table
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Standard SQL | Yes | Yes |
| Type of command | DML (Data Manipulation Language) | DDL (Data Definition Language) |
| Transactional | Yes (can be rolled back within a transaction) | No (cannot be rolled back) |
| Performance | Slower (due to row-by-row deletion)—can be optimized with a WHERE clause | Faster (deletes all rows in one operation)—does not support a WHERE clause |
| Logs | Fully logged (logs each deleted row) | Minimally logged (only logs deallocation of pages) |
| Index change | Yes (rebuilds indexes if needed) | No (does not rebuild indexes) |
| Trigger execution | Yes (fires any DELETE triggers) | No (does not fire DELETE triggers) |
| Foreign key constraints | Enforced (checks for referential integrity) | Not enforced (may fail if foreign key constraints exist) |
| Auto-increment and sequence reset | No | Yes (can reset auto-increment values) |
| Locking behavior | Row-level locks | Schema-level locks (Sch-M) |
| Where Clause | Supports WHERE clause for conditional removal | Does not support WHERE clause (removes all rows) |
Conclusion
In this guide, you explored the differences between DELETE FROM vs TRUNCATE. As demonstrated in the examples, working with record removal becomes much easier with a database client like DbVisualizer—as it enables you to view query results in real-time and visually understand which rows have been removed.
DbVisualizer is a powerful database client that fully supports DELETE, TRUNCATE, and all other SQL commands. It also offers advanced features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
What is the difference between TRUNCATE TABLE vs DELETE in SQL?
TRUNCATE TABLE is just another way of writing TRUNCATE, so the difference between TRUNCATE TABLE vs DELETE is the same as the difference between TRUNCATE and DELETE, as explained in the article
What is the difference between DELETE vs TRUNCATE vs DROP TABLE in SQL?
How to truncate many tables in SQL at once?
To truncate multiple tables in SQL at once, you generally have to execute separate TRUNCATE commands for each table:
1
TRUNCATE TABLE table_1;
2
TRUNCATE TABLE table_2;
3
TRUNCATE TABLE table_3;
Discover more in our guide on how to truncate many tables in SQL.
How to reset a sequence after a TRUNCATE statement in PostgreSQL?
In PostgreSQL, TRUNCATE does not automatically reset sequences. To achieve that goal, you need to specify the RESTART IDENTITY option:
1
TRUNCATE table_name RESTART IDENTITY;
For more guidance, read our PostgreSQL truncate table reset sequence tutorial.
Why use a visual database client when dealing with DELETE and TRUNCATE?
Working with record removal is much easier with a visual database client like DbVisualizer. It lets you interact with your data visually, providing instant results. Additionally, DbVisualizer offers features like query visualization and SQL formatting, making it simpler to work with databases. Grab a 21-day free trial today!
