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?

  • 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 R. 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 R. 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 !

| improve this answer | |

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