112

what's faster?

DELETE FROM table_name;

or

DELETE FROM table_name where 1=1;

why?

does truncate table work in access?

handle
  • 5,859
  • 3
  • 54
  • 82
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • 4
    For what database? For what table? For what size of data? – Brian Neal Jun 08 '10 at 20:13
  • 2
    What did you find out when you measured the time taken by both? – Greg Hewgill Jun 08 '10 at 20:13
  • 1
    You may also be interested in the answers to [Why would someone use WHERE 1=1 AND in a SQL clause?](http://stackoverflow.com/questions/242822/why-would-someone-use-where-11-and-conditions-in-a-sql-clause). – Greg Hewgill Jun 08 '10 at 20:24
  • 1
    Um, am I the only one noticing that this is an error using `DELETE * FROM` instead of `DELETE FROM`? I found someone accidentally put this in code, and was searching to see how common it was. I also made a test table and sure enough, no joy with the asterisk. – Exit Nov 21 '19 at 22:12
  • 1
    For MS-Access, `DELETE * FROM` is correct. This question is a mess, because it has accumulated answers both for [mysql] and [ms-access]. – Andre Dec 12 '19 at 10:33

6 Answers6

208

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
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
Jaymz
  • 6,140
  • 2
  • 26
  • 30
  • 4
    No, would have helped if you mentioned Access in your original question. In which case, DELETE FROM table_name will do the job quickest as answered below. – Jaymz Jun 08 '10 at 20:22
  • 4
    Bear in mind that `TRUNCATE` will reset all `AUTO_INCREMENT` counts on MySQL tables (not sure if MS Access has such a thing). – BoltClock Jun 08 '10 at 20:24
  • 3
    Access does not maintain a transaction log, which is why there's no need for the TRUNCATE statement, and why the DELETE FROM table_name will do the job nicely. – Jaymz Jun 08 '10 at 20:26
  • 3
    It should be noted that DELETE and TRUNCATE are not the same thing. TRUNCATE has side effects and typically requires more permissions to execute. – Donald Byrd Jun 01 '16 at 00:53
55

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
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • 1
    No, it's not supported in Access. – Jaymz Jun 08 '10 at 20:23
  • Jet treats DELETE * FROM Table as a truncate, instead of deleting the records one by one. I don't think it resets the Autonumber seed value, though. That has to be done in code or with a compact (not even sure it will reset with a compact in recent iterations of Jet/ACE). – David-W-Fenton Jun 24 '10 at 21:09
  • I can confirm that if all records are deleted from an Access table, a Compact will reset the Autonumber seed value in Access 2016. – Ben Mar 22 '18 at 12:36
18

This is deletes the table table_name.

Replace it with the name of the table, which shall be deleted.

DELETE FROM table_name;
Altay Akkus
  • 325
  • 1
  • 10
Oladimeji Ajeniya
  • 338
  • 4
  • 8
  • 1
    This doesn't delete the table, it deletes all the rows from the tables and allows you to then continue to use the table unaffected. This is perfect for me, thanks. – Spinstaz Oct 24 '21 at 15:58
12

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.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
1
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.

Juan Fernando Arango
  • 21
  • 3
-10

You can also use truncate.

truncate table table_name;
Altay Akkus
  • 325
  • 1
  • 10
  • 11
    This solution was included in at least three answers seven years before you posted this answer. Please take the time to read all existing answers before posting you rown. – TylerH Jan 28 '19 at 14:29