SQL Server trivia

I’m doing something really mind-numbing right now: Checking that my database lookup table data is correct and up to date. Correction: checking that the scripts to create my database lookup table data are up to date. Like I said, fun.

So here’s my trivia question:
In SQL Server what’s the difference between TRUNCATE TABLE [tablename] and DELETE TABLE [tablename]

Answer:
TRUNCATE TABLE doesn’t write each record being deleted in the log files, like DELETE TABLE does. It only records that the table data is being deleted, and that’s it.

Not much in terms of end result, but if you want to do a restore, you’re hooped. However, if you really want to be geeky about it, it does speed up the performance of the SQL quite significantly, especially if you have a large table to delete.

Of course, any good Database Administrator would tell you NEVER to delete any data, it’s just bad practice.