delete all from table
SqlMysqlMs AccessOptimizationPerformanceSql Problem Overview
what's faster?
DELETE FROM table_name;
or
DELETE FROM table_name where 1=1;
why?
does truncate table work in access?
Sql Solutions
Solution 1 - Sql
You can use the below query to remove all the rows from the table, also you should keep it in mind that it will reset the Identity too.
TRUNCATE TABLE table_name
Solution 2 - Sql
This should be faster:
DELETE * FROM table_name;
because RDBMS don't have to look where is what.
You should be fine with truncate though:
truncate table table_name
Solution 3 - Sql
This is deletes the table table_name.
Replace it with the name of the table, which shall be deleted.
DELETE FROM table_name;
Solution 4 - Sql
There is a mySQL bug report from 2004 that still seems to have some validity. It seems that in 4.x, this was fastest:
DROP table_name
CREATE TABLE table_name
TRUNCATE table_name was DELETE FROM internally back then, providing no performance gain.
This seems to have changed, but only in 5.0.3 and younger. From the bug report:
> > [11 Jan 2005 16:10] Marko Mäkelä > > I've now implemented fast TRUNCATE TABLE, which will hopefully be included in MySQL 5.0.3.
Solution 5 - Sql
TRUNCATE TABLE table_name
Is a DDL(Data Definition Language), you can delete all data and clean identity. If you want to use this, you need DDL privileges in table.
DDL statements example: CREATE, ALTER, DROP, TRUNCATE, etc.
DELETE FROM table_name / DELETE FROM table_name WHERE 1=1 (is the same)
Is a DML(Data Manipulation Language), you can delete all data. DML statements example: SELECT, UPDATE, etc.
It is important to know this because if an application is running on a server, when we run a DML there will be no problem. But sometimes when using DDL we will have to restart the application service. I had that experience in postgresql.
Regards.
Solution 6 - Sql
You can also use truncate.
truncate table table_name;