One of the questions I got asked today is what the difference is between the SQL commands DELETE and TRUNCATE.
They both are used to empty out a table, which is different from DROP (which deletes the table)
DELETE
DELETE FROM <table_name>
DELETE FROM <table_name> <where_clause>
Delete is used to remove rows from a table, it can remove all rows or you can specify a where clause to delete only certain rows. A COMMIT needs to be done after a delete and all delete triggers in the table will be fired.
DELETE FROM table1 WHERE column1=’value1′;
10 rows delete.COMMIT;
commit complete.
TRUNCATE
TRUNCATE TABLE <table_name>
Truncate is used to delete all rows from a table. It does not fire any triggers and there is no rollback. A truncate is therefore much faster than a delete
TRUNCATE TABLE table1;
table truncated.
