What is the difference between delete table & truncate table?

[Swati]

Hi,
What is the difference between delete table & truncate table?

Please let me know!

[opaper]

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)

[MindSpace]

Hi,

The Difference between TRUNCATE and DELETE commands.

TRUNCATE

1. Once u use TRUNCATE command then u cannot retrieve the data again from the table.
2. TRUNCATE removes all the rows from the Table.

DELETE

1. If u use DELETE Command then the data deleted can be retrieved when you ROLLBACK .
2. U can delete selected no of records from table using DELETE command.

For Ex:

DELETE FROM [TABLE_NAME];, deletes all the records from the table.
DELETE FROM [TABLE_NAME] WHERE [FieldName] > 10;, will delete records which have the higher values than 10 ..i.e works on the specified condition in the WHERE CLAUSE.

Hope got answer !

[Modifier]

TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

[Praetor]

DELETE and TRUNCATE differ in some more important and subtle ways than the auto increment counter.

The most important difference is DELETE operations are transaction-safe and logged, which means DELETEs can be rolled back. TRUNCATE cannot be done inside a transaction and can’t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.

DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.

These differences are not specific to mySQL — PostgreSQL, MS SQL Server, and Oracle behave more or less the same way.