Here's a list of differences. I've highlighted Oracle-specific features, and hopefully the community can add in other vendors' specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.

General Overview

If you want to quickly delete all of the rows from a table, and you're really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.

Various system-specific issues have to be considered, as detailed below.

Statement type

Delete is DML, Truncate is DDL (What is DDL and DML?)

Commit and Rollback

Variable by vendor

SQL*Server

Truncate can be rolled back.

PostgreSQL

Truncate can be rolled back.

Oracle

Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.

However, see Flashback below.

Space reclamation

Delete does not recover space, Truncate recovers space

Oracle

If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.

Row scope

Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.

Oracle

When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table's data is possible.

Object types

Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)

Data Object Identity

Oracle

Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.

Flashback (Oracle)

Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.

However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition

Use of FLASHBACK in Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

Privileges

Variable

Oracle

Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.

Redo/Undo

Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.

Indexes

Oracle

A truncate operation renders unusable indexes usable again. Delete does not.

Foreign Keys

A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.

Table Locking

Oracle

Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.

Triggers

DML triggers do not fire on a truncate.

Oracle

DDL triggers are available.

Remote Execution

Oracle

Truncate cannot be issued over a database link.

Identity Columns

SQL*Server

Truncate resets the sequence for IDENTITY column types, delete does not.

Result set

In most implementations, a DELETE statement can return to the client the rows that were deleted.

e.g. in an Oracle PL/SQL subprogram you could:

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;

All good answers, to which I must add:

Since TRUNCATE TABLE is a DDL (Data Defination Language), not a DML (Data Manipulation Langauge) command, the Delete Triggers do not run.

With SQL Server or MySQL, if there is a PK with auto increment, truncate will reset the counter.

"Truncate doesn't log anything" is correct. I'd go further:

Truncate is not executed in the context of a transaction.

The speed advantage of truncate over delete should be obvious. That advantage ranges from trivial to enormous, depending on your situation.

However, I've seen truncate unintentionally break referential integrity, and violate other constraints. The power that you gain by modifying data outside a transaction has to be balanced against the responsibility that you inherit when you walk the tightrope without a net.

Yes, DELETE is slower, TRUNCATE is faster. Why?

DELETE must read the records, check constraints, update the block, update indexes, and generate redo/undo. All of that takes time.

TRUNCATE simply adjusts a pointer in the database for the table (the High Water Mark) and poof! the data is gone.

This is Oracle specific, AFAIK.

In SQL Server 2005 I believe that you can rollback a truncate

A small correction to the original answer - delete also generates significant amounts of redo (as undo is itself protected by redo). This can be seen from autotrace output:

SQL> delete from t1;

10918 rows deleted.

Elapsed: 00:00:00.58

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1)
   1    0   DELETE OF 'T1'
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1)




Statistics
----------------------------------------------------------
         30  recursive calls
      12118  db block gets
        213  consistent gets
        142  physical reads
    3975328  redo size
        441  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10918  rows processed

The biggest difference is that truncate is non logged operation while delete is.

Simply it means that in case of a database crash , you cannot recover the data operated upon by truncate but with delete you can.

More details here

Here is a summary of some important differences between these sql commands:

sql truncate command:

1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions - provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle - Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

4) Frees up the space occupied by the data in the table (in the TABLESPACE - on disk). For Oracle - if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

5) TRUNCATE works much faster than DELETE

6) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states

7) Oracle - TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE

8) The TRUNCATE operation makes unusable indexes usable again

9) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table

11) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired

12) Oracle - TRUNCATE cannot be used in the case of: database link 13) TRUNCATE does not return the number of records deleted

14) Transaction log - one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) - faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs

15) Performance (acquired lock) - table and page lock - does not degrade performance during execution

16) TRUNCATE cannot be used on tables involved in transactional replication or merge replication

sql delete command:

1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

2) You can undo the operation of removing records by using the ROLLBACK command

3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

4) Does not free the space occupied by the data in the table (in the TABLESPACE - on the disk)

5) DELETE works much slower than TRUNCATE

6) Oracle Flashback works for DELETE

7) Oracle - For DELETE, you can use the GRANT command

8) The DELETE operation does not make unusable indexes usable again

9) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) DELETE requires a shared table lock

11) Triggers fire

12) DELETE can be used in the case of: database link

13) DELETE returns the number of records deleted

14) Transaction log - for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) - slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE

15) Performance (acquired lock) - record lock - reduces performance during execution - each record in the table is locked for deletion

16) DELETE can be used on a table used in transactional replication or merge replication

In short, truncate doesn't log anything (so is much faster but can't be undone) whereas delete is logged (and can be part of a larger transaction, will rollback etc). If you have data that you don't want in a table in dev it is normally better to truncate as you don't run the risk of filling up the transaction log

A big reason it is handy, is when you need to refresh the data in a multi-million row table, but don't want to rebuild it. "Delete *" would take forever, whereas the perfomance impact of Truncate would be negligible.

Can't do DDL over a dblink.

I'd comment on matthieu's post, but I don't have the rep yet...

In MySQL, the auto increment counter gets reset with truncate, but not with delete.

TRUNCATE is fast, DELETE is slow.

Although, TRUNCATE has no accountability.