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.TableWHERE <Condition>;
or
DELETE dbo.TableWHERE <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 ConditionIF @@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
