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.
Read on for more details and a couple scripts to test out Richie’s statements.