In our journey for new questions to ask on an interview I came up with an interesting question. Although it is not too difficult there are not too many people who really know the difference between DELETE (without WHERE) and TRUNCATE.

TRUNCATE is faster than DELETE is not a complete answer (although true), but what makes it faster.

DELETE removes the rows one at a time and records this in the transaction log. TRUNCATE removes them by deallocating the data and index pages and only this action is logged.

There are a couple of things to note though:
- If a foreign key references the table TRUNCATE will not work
- If the table is publishing data for replication TRUNCATE will not work
- Delete triggers are not fired

Many people believe that TRUNCATE is not logged, this is of course not true. You can use TRUNCATE in a transaction (try using it in a transaction and rollback that transaction).