Problem
While interviewing SQL Server candidates 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 tip we will cover some of these aspects.
Solution
I have been actively involved in the interview process for over 10 years and have interviewed more than 500 SQL DBAs from junior to senior levels. I follow a simple process when interviewing candidates starting from basic then going through advance topics. I don’t expect them to know all of the answers, but they should be aware of the basics and have worked on at least one or two areas like clustering, mirroring, replication, log-shipping, performance tuning, troubleshooting, monitoring, installation, migration, disaster recovery strategy, etc.
If you don’t know how SQL Server works you won’t know how to handle certain situations where expertise is needed. So I often start with a question like “What is the difference between Delete and Truncate?”, which I think is a very basic concept and every DBA must know. To my surprise, I have heard many shocking answers. One candidate told me truncate will delete the entire schema and all tables. I said what? And what about DROP? (silence)
So I decided to write about the differences between DELETE and TRUNCATE with an example, so people can have a better understanding.
Everyone should know that DELETE is DML command and TRUNCATE is 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 misconception 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 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 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.
Next Steps
- Check out these related tips
