Difference between drop table and truncate table?

Question

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

Answer 1

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

Answer 2

DROP TABLE deletes the table.

TRUNCATE TABLE empties it, but leaves its structure for future data.

Answer 3

DROP and TRUNC do different things:

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

DROP TABLE

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

As far as speed is concerned the difference should be small. And anyway if you don't need the table structure at all, certainly use DROP.

Answer 4

I think you means the difference between DELETE TABLE and TRUNCATE TABLE.

DROP TABLE

remove the table from the database.

DELETE TABLE

without a condition delete all rows. If there are trigger and references then this will process for every row. Also a index will be modify if there one.

TRUNCATE TABLE

set the row count zero and without logging each row. That it is many faster as the other both.