SQL DELETE TABLE Statement, Difference DELETE and TRUNCATE, Deleting Large Amounts Of Data

Last modified date:

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):

DELETE FROM dbo.Table
WHERE <Condition>;

or

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
   BEGIN
   DELETE TOP (1000) FROM [dbo].[Table]
   WHERE Condition
IF @@rowcount < 1000 BREAK;
END;

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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Handling SQL tables Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.