One of the most famous interview questions for novice database job seekers is to explain the difference between DELETE and TRUNCATE commands. Well, I have also faced it few times so I have decided to write an article to explain it. Sometimes they also add DROP command to explain along with DELETE and TRUNCATE but most of the times they ask about latter two because the DROP command is completely different. If you are new to write database queries, you must learn about the difference between DELETE and TRUNCATE commands because it is necessary to know where to use DELETE and where to TRUNCATE. Both are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.
DELETE is a Data Manipulation Language (DML) statement. It removes some or all rows from a particular table. When we need to remove some of the rows, we use WHERE clause with DELETE otherwise it is used only with asterisk (*) to delete all the rows of that table. DELETE command removes rows one by one so it takes time to remove all rows. DELETE command fires trigger and it is a logged operation. It means that the deletion of each row is logged and we can recover deleted rows. DELETE is not an auto-commit statement; in fact all DML statements are not auto-commit.
DELETE * FROM Customers;
DELETE * FROM Customers WHERE CustomerType = 5;
TRUNCATE is a Data Definition Language (DDL) statement. It removes all the rows of a specific table at a time. If we need to remove all the rows and don’t want keep any data in a table, we can use TRUNCATE command instead of DELETE. TRUNCATE command is also a logged operation but in a different way. It logs the de-allocation of the data pages in which data exists. TRUNCATE command doesn’t activate trigger so it may cause problem when some records need to be removed or updated in other tables using trigger. TRUNCATE rapidly removes all the rows of a table maintaining the table definition and structure while DROP does the same with removing table definition and structure as well.
TRUNCATE TABLE Customers
Difference between DELETE and TRUNCATE
- DELETE is a Data Manipulation Language (DML) statement while TRUNCATE is a Data Definition Language (DDL) statement.
- DELETE locks a single row and remove a single row at a time. TRUNCATE locks whole table not a row and removes all the rows at a time.
- WHERE clause can be used in DELETE but it cannot be used with TRUNCATE.
- When we remove all rows using DELETE, it doesn’t reset the IDENTITY column while TRUNCATE resets the IDENTITY column and gets fresh start from seed value.
- DELETE command activates the trigger but TRUNCATE command doesn’t activate trigger.
- DELETE doesn’t remove records permanently while TRUNCATE removes records permanently.
- Removing all the rows using DELETE command does not free the space while using TRUNCATE, the space will be free.
- DELETE is slower in performance as compare to TRUNCATE.
- In DELETE command, we can rollback data while rollback is not possible in TRUNCATE command without using TRANSACTION.
- DELETE statement uses more transaction space than TRUNCATE statement.