We distinguish two types of commands with data deleting purpose – SQL commands DELETE and TRUNCATE. Each is suitable for different situations. It is good to know them both and be able to choose the right one in a specific situation.

DELETE Syntax (First Command is Optional):

WHERE <Condition>;


DELETE dbo.Table
WHERE <Condition>;

Be Careful With Big Amounts of Records

We can use the command to delete records from a table and apart from TRUNCATE we can establish a WHERE condition. If the condition is not set, all records in a table will be deleted. The whole process is being logged into transaction log if we launch the DELETE command. That’s why it is better to use TRUNCATE in this scenario. Transaction log will not be under too much pressure and the operation will go on faster.

The operation can take a long time if we are deleting big amounts of records. Transaction log will also get quite inflated. Deleting bigger amounts of records can even result in lock of the whole table. If you would like to evade these problems, whenever using this command with big data amount, iterate after certain number of deleted records through loop:

Big data amount Syntax:

WHILE 1 = 1
   DELETE TOP (1000) FROM [dbo].[Table]
   WHERE Condition
IF @@rowcount < 1000 BREAK;

Script will be deleting only 1000 records at a time. Loop will get interrupted if the count of deleted files is lower than 1000 = last iteration was reached.

Syntaxe based on JOIN:

DELETE FROM [dbo].[Table]
FROM [dbo].[Table] AS [A]
INNER JOIN [dbo].[Table_2] AS [B]
ON [A].[ID] = [B].[ID]
WHERE [B].[Condition];

DELETE vs. TRUNCATE differences

Purpose of both commands is to delete records from the table. But there is number of differences between them

  • Transaction log – DELETE logs every deleted record into transaction log and the whole operation gets slowed down
  • Indexed View – DELETE statement apart from TRUNCATE can be applied on indexed view
  • Condition– where condition can be used with deleting, JOINS and table expression can be used
Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *