Difference between Delete and Truncate Command

Although the Delete and Truncate Commands logically does the same work of deleting the rows from the table but still there are many differences in their working. These differences are given below:-

1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Imp SQL Difference, Interview Questions, SQL Basic Concepts, SQL Server and tagged . Bookmark the permalink.

One Response to Difference between Delete and Truncate Command

  1. admin says:

    Please don't use my blog for your promotion…….

Leave a Reply