I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?

closed as unclear what you're asking by Colin 't Hart, Mr.Brownstone, Md Haidar Ali Khan, Vérace, Kin Shah Oct 29 '18 at 1:09

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    This is a very old topic, have you googled? – jyao Oct 27 '18 at 4:40
  • Yes, and the answer is a lecture and not concise. I'm just wondering what I should do in my specific situation for emptying a table repeatedly. – Niklas Rosencrantz Oct 27 '18 at 5:06
  • 2
    because there is no concise answer if you need to consider different scenarios. – jyao Oct 27 '18 at 5:11
  • @jyao I edited the title of this question to be my specific situation. I am running scripts for ETL and repeatedly need to empty a table. – Niklas Rosencrantz Oct 27 '18 at 5:17
  • Why do you need to do this? Do you need transactions? Which DBMS are you using? – Colin 't Hart Oct 27 '18 at 13:03

My short answer is:

Use truncate if you do not see any errors (because truncate cannot be used in many scenarios, such as the table is referenced or the table is in replication, but you will get error in such cases)

The advantages of truncate (over delete esp. in your repeated deletion case) are:

  1. fast as it does not involve big overhead of transaction logging.
  2. much smaller footprint on database log.

Hope this helps !

Not the answer you're looking for? Browse other questions tagged or ask your own question.