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:

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.

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.