Difference Between Delete and Truncate Commands in SQL Server ?

Delete is a DML Command and Truncate is a DDL Command.
Delete and Truncate both are used for deleting the data from database tables.
Delete and Truncate have some differences and it performed differently on deleting the data.

Below is the Tabular Representation of the Differences in Delete and Truncate.

Sr.No Delete Truncate
1 Delete is Slower than truncate Truncate is faster than Delete
2 It Delete data from tables based on some conditions It removes whole data from tables when we execute it
3 It deleted one row at a time or we can say that deleted row by row All the rows are deleted together
4 Delete is DML Command Truncate is DDL Command
5 Delete can be Roll Back Truncate can not be Roll Back
6 It does not reset the Identity of the table It reset the Identity of the table
7 Delete activates the Triggers on table Truncate is not activated the Triggers
8 For each deleted row records the entry in transaction logs Only Page deallocation recorded in transaction logs
9 We can use "Where" with Delete We can not use "Where" with Truncate
10 We can Delete data from table that is referenced for a Foreign Key We can not truncate data from table that is referenced for a Foreign Key


Image is not available

About the Author
Sudheer Singh Chouhan is a Software Engineer having Expertise in Development Design and Architecting the applications , Project Management , Designing Large Scale Databases in SQL Server since last 17 Years.
Skill Sets :- Microsoft .NET technologies like ASP.Net Core, Web API, LINQ, Web Forms, WinForms, SQL Server, EntityFramework, Design Patterns, Solid Principles, Microservices, AWS Cloud.