TRUNCATE TABLE: A possible alternative for the DELETE statement

While reviewing an archiving script at my company I realised that most of the time spent waiting for the script to complete was due to the deletion process. While trying to find a better way to perform the deletion of data from the database I came across the DDL statement: TRUNCATE TABLE.

The TRUNCATE TABLE and DELETE statements can be used to remove records from a table, the difference is that DELETE can be used to remove some or all of the data in the table while TRUNCATE TABLE can only be used to remove all the data from a table (no WHERE clause). However TRUNCATE TABLE is faster than the DELETE statement and requires fewer system and transaction logs resources because of the difference in how they execute and operate. TRUNCATE TABLE removes data by deallocating data pages instead of removing rows one at a time.

I decided to do a comparison of the two statements using a test table with 3.4 million rows.

The results for each script/statement had four grids:

• The first result grid shows the size of the MDF and LDF before the process.
• The second result grid shows the number of records in the table before the DELETE/TRUNCATE TABLE statement is executed.
• The third result grid shows the number of records in the table after the DELETE/TRUNCATE TABLE statement is executed.
• The fourth result grid shows the size of the MDF and LDF after the process.

DELETE Statement

Script

Results

Notice the increase sized of LDF after the process (from 5.5625 MB to 2321.375 MB). The script was executed three times and each time the LDF was the increased to the same size.

The execution time results:

 SQL Server Execution Times:

   CPU time = 6177 ms,  elapsed time = 114451 ms.

 SQL Server Execution Times:

   CPU time = 6349 ms,  elapsed time = 105675 ms.

 SQL Server Execution Times:

   CPU time = 6193 ms,  elapsed time = 105816 ms.

TRUNCATE TABLE Statement

Script

Results

Notice no change to the size of the MDF or LDF. The script was also executed three times.

The execution time results:

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 140 ms.

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 96 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 191 ms.

Conclusion:

Based on the execution times results it is very clear that the TRUNCATE TABLE statement is faster than the DELETE statement.  It is also clear that the DELETE statement process of removing rows one at a time requires more transaction log space when compared to the TRUNCATE TABLE process.

See the following MSDN links to understand for more information about TRUNCATE TABLE and DELETE.