What do you do when you want to delete all rows from a table?
I do this:
DELETE FROM my_table;When you don't provide a WHERE condition, all the rows are deleted from the given table. That's correct and it's simple, and works.
Today I found about the TRUNCATE statement, I've heard about it but I had never used it in any project. TRUNCATE is a better alternative to the DELETE statement when you are sure that you don't want your data back:
- The transaction log isn't used (depends on the database implementation, but if something is logged it should be minimal).
- TRUNCATE locks the full table while DELETE typically locks each row at a time, so the deletion can be really faster with TRUNCATE.
- All pages are deleted with TRUNCATE (no empty pages are left)
The syntax for the TRUNCATE statement is:
TRUNCATE TABLE my_table;
TRUNCATE is defined by the SQL standard, but there many details left to each vendor to decide. So far my two of my three common RDBMS that I use implement it: Oracle and SQL Server implement TRUNCATE, and I was surprised to find that DB2 does not support TRUNCATE under Windows and Linux (but seems like the mainframe version does support it).
Each time I need to clean up my test/dev databases, I'll try to use TRUNCATE, I don't know if I'll want to use it in production level databases.
