DELETE

The SQL Standard description of the typical DELETE format is:

 DELETE FROM <Table name>   [ WHERE <search condition> ] 

The word FROM is optional for Microsoft and Sybase, but once again, there is no reason to leave it out and cause portability trouble. Here's an example:

 DELETE FROM Table1   WHERE column1 = 55 

If all rows in the table are to be deleted, it's a better idea to execute DROP TABLE and then re-create the table, or to use a nonstandard SQL extension row- blaster . For example Informix, Microsoft, and Sybase allow:

 TRUNCATE TABLE <Table name> 

And Ingres allows:

 MODIFY <Table name> TO TRUNCATED 

Such statements, however, do not allow for logging or for DELETE triggers. But TRUNCATE has advantagesit won't cause dropping of related constraints, indexes, or optimizer statistics. Because you usually won't want these objects to be dropped in addition to the table, TRUNCATE involves less maintenance at rebuild time.

The Bottom Line: DELETE

If all rows in the table are to be deleted, it's a better idea to execute DROP TABLE and then re-create the table or to use TRUNCATE.

TRUNCATE involves less maintenance.