SQL Server Truncate VS Delete

Many a times, we come across a situation wherein we need to understand the difference between a SQL Server Truncate VS Delete Command In SQL Server.In this exclusive Blog Post, Satnam Singh, an SQL Server DBA from L&T Infotech,Mumbai,India discusses one such difference between both the commands.

As per Books Online, the difference between Truncate and Delete commands is as follows:

  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
  • You can use WHERE clause(conditions) with DELETE but you can’t use WHERE clause with TRUNCATE .
  • You cann’t rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
  • If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
  • Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
  • TRUNCATE is faster than DELETE.

In this blog post, we shall be focussing on Point no 3 mentioned above i.e. We can’t RollBack, once Truncate statement has been fired.

For demonstration purpose, I have created a database named Student using the below T-SQL statement,

Create Database Student

In the database named Student, Let’s create a table named Student_Data as follows:

create table student_data
(
student_id int,
student_first_name varchar(50),
student_last_name varchar(50)
)

Now we insert data of 5 students into the table named student_data using the below T-SQL,

insert student_data
select 1,'Prateek','Arora'
union
select 2,'Sharanjeet Singh','Sansoya'
union
select 3,'Amit','Singh'
union
select 4,'Arti','Samkaria'
union
select 5,'Khushboo','Jatav'

Once these 5 records are inserted into the table we will take a Full Backup of the database using the below T-SQL.

BACKUP DATABASE student
TO DISK='F:\Database_Backups\Student_Full_Backup.bak'

Once the Full Backup of the database is completed, we insert 2 more records into the table using the below T-SQL.

insert student_data
select 6,'Kuldeep','Singh'
union
select 7,'Purvish','Shah'

Once the above 2 records are inserted into the table we will take the Differential Backup of the database using the below T-SQL.

BACKUP DATABASE student
TO DISK='F:\Database_Backups\Student_Differential_Backup.bak'
WITH DIFFERENTIAL

Once the Differential Backup of the database gets completed, we insert 1 more record into the tabe using the below T-SQL.

insert student_data
select 8,'Hemal','Joshi'

Once the above T-SQL statement got executed, we fire a TRUNCATE statement as follows:

TRUNCATE TABLE STUDENT_DATA

Now, In order to Recover the data back, We would first take the Transactional Log Backup of the database using the below T-SQL,

BACKUP LOG student
TO DISK='F:\Database_Backups\Student_TransactionalLog_Backup.trn'

Now, Just FYI, the Truncate Statement was fired at 09:45:44 hrs on 18-June-2012.

Now Let’s try to Recover the data back.

We will first Restore the Full Backup with NORECOVERY mode so that we can apply additional backups over it, Full Backup is Restored using the below T-SQL,

RESTORE DATABASE Student_Backup
FROM DISK='F:\Database_Backups\Student_Full_Backup.bak'
WITH MOVE 'student' TO 'D:\Satnam\student.mdf',
MOVE 'student_log' TO 'D:\Satnam\student_log.ldf',
NORECOVERY,STATS=10
GO

Once the Full Backup gets Restored,we will Restore the Differential Backup over it using the below T-SQL,

RESTORE DATABASE Student_Backup
FROM DISK='F:\Database_Backups\Student_Differential_Backup.bak'
WITH
NORECOVERY,STATS=10
GO

Now, we will try to Restore the Transactional Log Backup over it but to a point in time using the below T-SQL:

RESTORE LOG Student_Backup
FROM DISK='F:\Database_Backups\Student_TransactionalLog_Backup.trn'
WITH
RECOVERY,STATS=10,
STOPAT = 'June 18, 2012 09:44:43 AM'
GO

Once the above T-SQL gets executed successfully, we now query the student_data table in the student_backup database using the T-SQL below,

SELECT * FROM STUDENT_DATA

The output can be viewed in the screen capture below

1_SQL_Server_Truncate_VS_Delete

From the above screen capture, we can conclude that once a TRUNCATE Statement is Fired, Data cannot be Rolled Back, means No Point in Time Recovery is possible.

Now Consider another case instead of a TRUNCATE, a Delete statement is fired without a WHERE clause and we try to Recover it using the below strategy. Just FYI, the DELETE statement was fired at 11:14 AM on 18-June-2012. We first take the Transactional Log Backup of the Database using the below T-SQL,

BACKUP LOG student
TO DISK='F:\Database_Backups\Student_TransactionalLog_Backup.trn'

Now, In order to Recover the Data Back, we will follow the below steps which involves Restoring the Database Backups in a sequence:

  • First,we will Restore a Full Backup using the below T-SQL.
RESTORE DATABASE Student_Backup
FROM DISK='F:\Database_Backups\Student_Full_Backup.bak'
WITH MOVE 'student' TO 'D:\Satnam\student.mdf',
MOVE 'student_log' TO 'D:\Satnam\student_log.ldf',
NORECOVERY,STATS=10
GO
  • Once the Full Backup is Restored, we will try to Restore the Differential Backup as below:
RESTORE DATABASE Student_BackupFROM DISK='F:\Database_Backups\Student_Differential_Backup.bak'WITH NORECOVERY,STATS=10
GO
  • This step involves Restoring the Transactional Log Backup using the below T-SQL.
RESTORE LOG Student_Backup
FROM DISK='F:\Database_Backups\Student_TransactionalLog_Backup.trn'
WITH
RECOVERY,STATS=10,
STOPAT = 'June 18, 2012 11:14:00 AM'
GO

Once the database named student_backup comes ONLINE,we would then query the student_data and examine the OUTPUT, please refer the below screen capture:

2_SQL_Server_Truncate_VS_Delete

Thus from the above screen capture,we can conclude that if a DELETE is fired without a WHERE clause it is possible to RECOVER the data back,whereas if a TRUNCATE is fired then we cannot ROLLBACK. Ideally, one should use a TRUNCATE statement in case where the data is a test data and you can afford to throw it away.

Many to all the viewers for giving their valuable time in reading the Blog post. Please do let us know any suggestions Thanks which would have made the Blog Post much better.

 

Regards

Satnam Singh

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook

10 Comments on “SQL Server Truncate VS Delete”

  1. whats is the main diference between delete and truncate?? why recovery is possible in delete and why not in truncate ?

  2. In order to RollBack a transaction it has to be logged which is possible only in case of Deletes. Truncate never logs the removed records and hence can’t be Rolled Back.

  3. Nice article.. to add to this, Truncate can be rolled if it’s inside an Explicit transaction as in

    Begin tran

    truncate

    Rollback

  4. I think you need to be careful about your terminology. It is not true that a TRUNCATE TABLE cannot be rolled back. If you start a transactaction, issue a TRUNCATE TABLE command, and then ROLLBACK TRAN, the TRUNCATE will be rolled back.

    What you are talking about is better termed ‘recovery’. A TRUNCATE TABLE command can’t be recovered once a COMMIT has been issued, as you demonstrate. The word ‘rollback’ has a specific meaning in SQL and it isn’t ‘restore and rollforward’.

    There are many cases where TRUNCATE is useful for production code and data. You just have to understand the implications.

  5. nice article satnam…..

    Working of Truncate Command :

    When we fire truncate command for a particular table it deallocate all the pages for that table, and transaction log have only entry of page deallocation..

  6. another thing that i found out while at work today, There is no permission or privilege that you can grant a user to truncate a table. User should be owner of table/database to be able to truncate a table or syadmin obviously or at minimum should have alter table privilege.

    Where as DELETE can be granted/revoked from a user for a table/database.

    let me know if i am wrong…

  7. With respect to TRUNCATE and DELETE —

    Both TRUNCATE and DELETE Statement(s) can be rolled back when put into an Explicit TRANSACTION.

  8. Thanks Satnam..for this wonderful Article…

    In Both case..Truncate or delete in begin trasaction mode…whenever we not commited the transaction we can roolback the data and use for as usual.

  9. Hi,

    I did not understand why we cant recover data after truncate using backups. This is really incorrect information.

    I tried the same steps , with the same scripts and I’m able to restore the data.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *