How To Decide Whether To Use Delete Or Truncate In SQL Server

While deleting data from a large SQL table, a question always comes to mind: what should we use? Delete or Truncate. With the contents published in this article, you can easily get your answer.

Introduction

Sometimes while handling large databases, we get stuck between Truncate and Delete statements. Each one has its pros and cons and it's a really crucial decision to decide which one to use. Before we go ahead and understand the answer to this question, let's first understand the differences between these two statements.

Delete

Syntax

 

Delete from MST_Team;

1. Delete is a DML command unlike truncate. Truncate is DDL command.
2. Delete statement deletes records row by row and maintains a log of each deleted row in the transaction log.
3. A row lock is applied on the table for each row getting deleted.
4. It can also be used with where clause to delete records based on matching conditions.
5. Identity retains its value even after deletion of records.
6. Delete can be used with indexed views.
7. Delete can also be used with foreign key references, records that are not interlinked with foreign key references will get deleted.
8. Delete uses more transactional space than a truncate statement.

Truncate

 

Syntax

 

Truncate table MST_Team;

1. Truncate is a DDL command.
2. Records in the whole table deleted at the same time. A table lock is applied while executing of the truncate statement.
3. No transactional log is maintained while deleting records with Truncate statement however page deallocation log is maintained.
4. The Identity of the column is set to its seed value.
5. Truncate can not be executed with foreign key references also it can not be used with indexed views.
6. No transactional log is maintained so Truncate is faster than Delete and also it uses less transactional space.
7. No where clause can be used, the data of the whole table gets deleted together. We can delete data partially with partitions.

From the above explanation of delete and truncate, we can easily conclude on the below scenarios.

Delete all records from a table: Which statement to use? Truncate/Delete

• To delete all records from the table, Truncate is the best option, however, it also has its own limitations as mentioned below.

1. On the Truncate table the Identity column is set to it's seed value. If you don't want this to happen, then we can not use Truncate even if you want to delete all records.
2. No transaction log is maintained. If you want to maintain transactional log then we can not use Truncate.
3. To Truncate tables we need minimum ALTER Table rights on the table. Delete rights aren't sufficient to Truncate the table. Sometimes we may not want to give ALTER rights because of security reasons.
4. Even if a single record has a foreign key, the Truncate statement won't get executed.

• We can use delete as well but it also has its own limitations, as mentioned below:

1. The Delete statement as mentioned above uses more transactional space.
2. The Delete statement is slow as it maintains all logs.

Delete a few records from the table: Which statement to use? Truncate/Delete

The answer to this question is very straight forward. It's the "DELETE" statement only, but hold on, there is some trick to delete partial records with Truncate.

We can delete partial data from the table with a Truncate statement using partitions. If logical partitions are applied on a table and if you want to delete a few partitions amongst all of them, then we can do the same.