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:
![SQL Server DELETE with Rollback]()
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 DELETE with Rollback]()
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:
![SQL Server TRUNCATE with Rollback]()
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.
![SQL Server TRUNCATE with Rollback]()
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
- 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.