Difference Between DELETE and TRUNCATE

• DELETE is DML command and TRUNCATE is DDL command.
• DELETE deletes records one by one and makes an entry for each and every deletion in the transaction log.
• TRUNCATE de-allocates pages and makes an entry for deallocation of pages in the transaction log.
• We can rollback DELETE and TRUNCATE if the commands are started inside a transaction.
• Truncate reseeds identity values, whereas delete doesn't.
• Truncate removes all records and doesn't fire triggers.
• Truncate is faster compared to delete as it makes less use of the transaction log.
• Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.

DELETE FROM [MyTable]
WHERE [Id] = '1';

DELETE [T]
FROM [MyTable] AS [T]
WHERE EXISTS
(
	SELECT * FROM [MyTable] AS [T1]
	WHERE [T1].[Id] = [T].[Id]
	AND [T1].[IsTest] = 1
);

TRUNCATE TABLE [MyTable];