Problem
what’s faster?
DELETE FROM table_name;
or
DELETE FROM table_name where 1=1;
why?
Is it possible to truncate a table in Access?
Asked by Alex Gordon
Solution #1
You can remove all the rows from the table with the query below, but bear in mind that it will also reset the Identity.
TRUNCATE TABLE table_name
Answered by Jaymz
Solution #2
This should be a lot quicker:
DELETE * FROM table_name;
because RDBMS don’t need to know where everything is.
However, you should be OK with truncate:
truncate table table_name
Answered by Sarfraz
Solution #3
This removes the table table name from the database.
Replace it with the table’s name, which will be removed.
DELETE FROM table_name;
Answered by Oladimeji Ajeniya
Solution #4
There is a bug report for mySQL from 2004 that still appears to be valid. This appears to be the fastest in 4.x:
DROP table_name
CREATE TABLE table_name
Internally, TRUNCATE table name was DELETE FROM, which provided no performance benefit.
This appears to have changed, but only in versions 5.0.3 and below. According to the bug report:
Answered by Pekka
Solution #5
TRUNCATE TABLE table_name
Because it’s a DDL (Data Definition Language), you can clean up your identity and delete all of your data. You’ll need DDL privileges in the table to use this.
CREATE, ALTER, DROP, TRUNCATE, and other DDL statements are examples.
DELETE FROM table_name / DELETE FROM table_name WHERE 1=1 (is the same)
You can remove all data because it is a DML (Data Manipulation Language). SELECT, UPDATE, and other DML statements are examples.
This is vital to know since if an application is running on a server, there will be no issues when we run a DML. When utilizing DDL, however, we may need to restart the application service. In postgresql, I had a similar situation.
Regards.
Answered by Juan Fernando Arango
Post is based on https://stackoverflow.com/questions/3000917/delete-all-from-table