I am reading DBMS concepts and i have some confusion about TRUNCATE and DROP. I know TRUNCATE does not affect structure, but what are other differences? When its better to use TRUNCATE and when DROP is better?
In some website it is written that truncate is faster but no one explained why.
There is lots of information available on the difference of both ( see here). However for the second part of your question regarding the performance - you should not worry that much for truncate vs drop. The reason being is it's more about the functionality you are expecting from truncate vs drop. You 'drop' when you don't intend to use the table anymore. You 'truncate' when you just want the table definition intact , you just want to delete the table data fast. The perceived difference in performance may come from the implementation details ( which can sometime be a bug like this) in the database.
TRUNCATE removes all the contents of the table, but keeps the structure of the table intact. Besides data, tables have indexes, constraints, triggers, and partitions for instance.
DROP drops the tables and the associated metadata, including indexes, constraints, and triggers.
You use TRUNCATE when you want to reload the table with new data. You use DROP when you don't want the table anymore.
A third possibility is DELETE to remove all the rows. Although the end result is the same as TRUNCATE, In most databases, this logs every row deletion and triggers delete triggers, so it is more expensive than TRUNCATE.
