SQL Server - Truncate V/S Delete

Many a times, we come across a situation wherein we need to understand the difference between a Truncate and 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:

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:

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 Thanks to all the viewers for giving their valuable time in reading the Blog post. Please do let us know any suggestions which would have made the Blog Post much better. Please do like us on facebook as well, www.facebook.com/sqlservergeeks