What’s the Difference Between TRUNCATE and DELETE?
DELETE can delete specific records, TRUNCATE deletes all recordsThere is no WHERE clause on the TRUNCATE statement. This is because all of the data in the table is removed when you run a TRUNCATE statement.
With the DELETE statement, you can delete all records, or use the WHERE clause to delete some records.
DELETE Allows You To Rollback, TRUNCATE Does Not Allow Rollback
With the DELETE statement, you can run a ROLLBACK statement and effectively restore the data you have deleted.
DELETE generates an undo log, TRUNCATE does not
This is what makes TRUNCATE statements faster than DELETE statements. When you run a DELETE statement, the undo log is generated so that the ROLLBACK statement can be run.
DELETE takes locks on rows, TRUNCATE does not
When you run a DELETE statement, the rows are locked while they are deleted. With the TRUNCATE statement, they are not locked.
DELETE causes delete triggers to fire, TRUNCATE does not
If you use triggers, this might cause different behaviour in your database.
DELETE is a DML command, TRUNCATE is a DDL command.
This difference means that DELETE can be rolled back and TRUNCATE cannot.
