I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:
- The TRUNCATE TABLE statement is a DDL operation, whilst DELETE is a DML operation.
- TRUNCATE Table is useful for emptying temporary tables, but leaving the structure for more data. To remove the table definition in addition to its data, use the DROP TABLE statement.
- TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
- TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So it’s important to understand that TRUNCATE is logged, just not at the row level.
- Because TRUNCATE TABLE is not fully logged, it cannot activate a trigger.
So it’s a lot faster as it skips logging the removal of data, which means any restore of a TRUNCATE table would have to take place from the last FULL backup. However, as TRUNCATE merely de-allocates the data pages this means that the data is still there and readable, so you can read the data pages (using some 3rd party tool like ApexSQL Recover) if you’re quick enough.
But how fast is “faster”? The below script creates a table, inserts some random data and then truncates the table with IO and TIME stats switched on. It then repeats the process but this time executes a DELETE instead of TRUNCATE.
This takes a little while to run, so the results are below the SQL.
View the code on Gist.
The TRUNCATE completely skips any scan; it doesn’t even touch the table, whilst the DELETE scans the table before taking any action. As the number of rows increase, so the time taken to DELETE will increase, whilst executing a TRUNCATE will not take any longer.
I’m glad that I looked into how a truncate works, rather than just accepting the way that things are. Before I looked into this I didn’t realise that restoring a table that had been truncated is somewhat different from restoring data that had been deleted, and in fact whilst functionally similar, the methods used to remove the data from the database are completely different.
