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,

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

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

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

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

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

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

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

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

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,

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

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

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,

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,

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.

  • Once the Full Backup is Restored, we will try to Restore the Differential Backup as below:

  • This step involves Restoring the Transactional Log Backup using the below T-SQL.

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