Differences Between Delete and Truncate in SQL Server

In interviews I find that most are not aware of some of the basic differences between the SQL Server delete and truncate commands as well as whether these operations can be rolled back, so in this blog we will cover some of these aspects.

We should know that DELETE is a DML command and TRUNCATE is a DDL command. DELETE deletes records one by one and makes an entry for each and every deletion in the transaction log, whereas TRUNCATE de-allocates pages and makes an entry for de-allocation of pages in the transaction log.

There is also a lot of misunderstanding among people about rolling back after a TRUNCATE or DELETE. People say DELETE can be rolled back, but TRUNCATE can't be rolled back. Is that true, even if we start a transaction? Let's try and find out. 

Let's create a table and insert a  few dummy records for testing:

CREATE TABLE Employee   
(   
Empid int NOT NULL,   
Name nchar(10) NULL,   
City nchar(10) NULL   
) ON [PRIMARY]   
GO   
--Command(s) completed successfully.    
insert into Employee values (1,'Shweta','Pune') ,(2,'Stella','Hydrabad')    
-- (2 row(s) affected)   
select * from Employee

SQL Server DELETE with Rollback

Now we have a table with dummy records. Now let's do a DELETE inside a TRANSACTION and see if we can rollback:

BEGIN TRANSACTION            
--select * from employee DELETE from Employee where Empid='1' SELECT * from Employee     
  GO  

We deleted the record where the Empid equals 1 and now we have only one record:

Let's try to rollback and see if we can recover the deleted record:

ROLLBACK TRANSACTION    
SELECT * from employee  

As you can see below, we have the record back.

SQL Server TRUNCATE with Rollback

Let's try the same for TRUNCATE:

begin transaction   
truncate table Employee    
select * from Employee 

Now we have truncated the table and have no records, the table is empty:

Let's try to rollback and see if we can get the records back. Run the below command and see what you get:

ROLLBACK TRANSACTION    
select * from Employee

As you can see below, we got the records back.

So we can rollback DELETE as well TRUNCATE if the commands are started inside a transaction and there is no difference between DELETE and TRUNCATE if we are talking about rollback. Try it on your own and let me know if you experience any issues.

Differences between the SQL Server DELETE and TRUNCATE Commands

1. Truncate reseeds identity values, whereas delete doesn't.
2. Truncate removes all records and doesn't fire triggers.
3. Truncate is faster compared to delete as it makes less use of the transaction log.
4. Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.