Difference between Truncate vs Delete Statement

In this blog, we will take a closer look at Truncate and Delete statements, understand their features and differences, and see when to use each of them.

Introduction

Truncate and delete are two commands that can be used to eliminate data in a database. While they both serve the same purpose, they have some distinctions that make them more fitting for certain scenarios.

What is Truncate? 

The truncate statement is used to clear all data from a table. It is faster than the delete statement, and it doesn’t require any maintenance operations after its execution. In other words, truncating a table will not affect any future inserts or updates made on your database tables.

Truncating a table permanently removes all its rows from the database but does not delete them from RAM because SQL Server stores each row as an object in memory (rather than just storing its primary key value). If you want to release those objects back into memory space again they must be retrieved by their primary key values when needed again; this requires additional work on behalf of developers who build applications using this feature in their databases.

An example of using TRUNCATE to delete all rows from the ’employees’ table:

TRUNCATE TABLE employees;
Code language: SQL (Structured Query Language) (sql)

Features of Truncate

• Truncate is a DDL statement which is used to remove all the data from a table.
• It can be used to remove all the data from a table in one step.
• Truncate removes all the rows from a table and frees up the space occupied by the table.
• It resets the identity column to its initial value if there is one.
• It is faster than Delete, as it does not have to scan the rows to be deleted.
• Truncate does not generate any transaction logs and hence it is faster than Delete.

Advantage of Truncate

• It’s used to remove all the data in a table, and
• It’s much faster than the delete statement because it doesn’t have to physically remove rows from your database.
• It is suitable for deleting large amounts of data, as it is faster and more efficient.
• Truncate does not generate any transaction logs, which makes it faster and more efficient.

Disadvantages of Truncate

There is a major disadvantage of the Truncate statement.

• The truncate command removes all data from a table, and
• It does not allow you to specify which columns are removed.
• It is not reversible, unlike the Delete statement.
• It cannot be used with a WHERE clause, which means that selective rows cannot be deleted.

What is a delete Statement?

The delete statement is used to delete specific rows from a table. It can be used to delete data from a table, or it can be used to explicitly remove all rows in the current table. The syntax of this command varies depending on whether you are using an access database, SQL Server, Oracle Database or MySQL.

To delete selective rows from a table ’employees’ using DELETE:

DELETE FROM employees WHERE salary < 25000;
Code language: SQL (Structured Query Language) (sql)

Features Of Delete Statement

• Delete allows you to delete selective rows from a table using a WHERE clause.
• It is a DML statement and generates transaction logs, which means you can roll back the changes made by a Delete statement.
• Delete is slower than Truncate, as it has to scan the rows to be deleted.

Advantages of Delete Statement

Delete Statement has the following advantages:

• It’s faster than Truncate for small sizes of data. The delete statement is much faster to execute than truncation, as it does not have to convert data from one type to another when deleting records. However, you can use Truncate if your application requires fast deletion of large amounts of data from an existing table.
• It’s more efficient than a Truncate statement with a small size of data and less system intensive than truncation. Because it does not make any changes in the underlying structure at all! This means that there will be no need for extra processing power or memory consumption by the database engine itself.
• Delete allows you to delete selective rows from a table using a WHERE clause.
• It generates transaction logs, which means you can roll back the changes made by a Delete statement.

Disadvantages of Delete statement

• You can’t use the Delete statement to delete all the data from a table.
• The Delete statement is not reversible, so you’ll have to go through the process of removing your data again if you want to remove it in bulk.
• If you’re trying to delete some large amount of rows at once, then this statement won’t work for you because it would take too much time and resources on your part (and possibly even crash).
• Delete is slower than Truncate, as it has to scan the rows to be deleted.
• It generates transaction logs, which can make it slower and less efficient for deleting large amounts of data.

Key Differences Between Truncate and Delete Statements

Truncate	Delete
Truncate is used to delete all records from a table	Delete is used to delete a single record from a table
Truncate is faster than Delete	Slower than truncate.

But both are faster than rebuilding the indexes.

Here are examples of using the DELETE and TRUNCATE statements in SQL on a given employees table:

DELETE FROM employees WHERE employee_id = 123;
Code language: SQL (Structured Query Language) (sql)

This DELETE statement will delete the row in the employees table where the employee_id column has a value of 123.

TRUNCATE TABLE employees;
Code language: SQL (Structured Query Language) (sql)

This TRUNCATE statement will delete all rows in the employees table and reset the auto-incrementing counter for the primary key to 0.

Note that the TRUNCATE statement is generally faster than the DELETE statement because it does not generate any undo/redo logs and does not fire any delete triggers. However, TRUNCATE is not suitable for deleting selective rows from a table, as it will delete all rows from the table. DELETE can be used for this purpose.

Major Differences between truncate and Delete Statement

Truncate	Delete
The truncate statement is used to remove all the data from a table or reset the value of an identity column in a table to its initial default value.	It is used to delete all the rows from a table.
More efficient for large data.	May be efficient for small sizes of data.
It’s a DDL statement.	It’s a DML statement.

Let’s see examples of using the DELETE and TRUNCATE statements on a given products table in SQL:

DELETE FROM products WHERE category = 'clothing';
Code language: SQL (Structured Query Language) (sql)

This DELETE statement will delete all rows in the products table where the category column has a value of ‘clothing’.

TRUNCATE TABLE products;
Code language: SQL (Structured Query Language) (sql)

This TRUNCATE statement will delete all rows in the products table and reset the auto-incrementing counter for the primary key to 0.

Conclusion

Truncate and Delete statements are two very different statements that can be used in SQL. The most obvious difference between them is that truncate is a statement that truncates data while a Delete statement is an action that removes data from the table. In addition, Truncate has been around since SQL Server version 2 whereas Delete statement was introduced in 2005.

FAQs

What are the 2 differences between DELETE and TRUNCATE?

• One of the main differences is that DELETE allows you to delete selective rows from a table using a WHERE clause, while TRUNCATE removes all the rows from a table.
• Second is that TRUNCATE is a DDL statement, while DELETE is a Data Manipulation Language (DML) statement.

What is the difference between DELETE and TRUNCATE SQL statements?

The difference between both of them is that DELETE is used to manipulate the data stored in a database. While TRUNCATE is used to define the structure of a database.

Which is better TRUNCATE or DELETE?

As for which is better – TRUNCATE or DELETE – it depends on the situation. TRUNCATE is faster and more efficient for deleting large amounts of data, as it does not have to scan the rows to be deleted and does not generate any transaction logs. However, it cannot be used with a WHERE clause and cannot be rolled back. On the other hand, DELETE allows you to delete selective rows and generates transaction logs, which means you can roll back the changes made by a DELETE statement. However, it is slower than TRUNCATE and may not be suitable for deleting large amounts of data.

Which is faster TRUNCATE or DELETE?

In terms of performance, TRUNCATE is faster than DELETE, as it does not have to scan the rows to be deleted and does not generate any transaction logs.

TRUNCATE or DELETE: which is fast?

However, DELETE may be faster for deleting small amounts of data, as it allows you to delete selective rows and does not reset the identity column (if there is one).