SQL TRUNCATE TABLE - TRUNCATE vs DELETE Commands
SQL TRUNCATE TABLE statement used to completely remove all table records. Not supporting to a WHERE clause.
SQL TRUNCATE statement is faster and use some transaction log resources.
SQL TRUNCATE statement logically equivalent to a DELETE statement that deletes all rows, but they are practically different under some rules.
SQL TRUNCATE Statement Rules
- TRUNCATE operation use for dropping or re-create table, which is much faster than deleting rows one by one.
- TRUNCATE operation not rollback, It means truncated can not be returned.
- TRUNCATE operation is not a safe.
Considering following syntax that help you to understanding TRUNCATE,
TRUNCATE TABLE table_name;
Example Statement :
SQL> TRUNCATE TABLE emp_data;
||If we want to delete all the records from the table, then our SQL query is:
SQL> DELETE FROM table_name;
|Whereas Truncate command is used to delete all records from the table. Our SQL query is:
SQL> TRUNCATE TABLE table_name;
||DELETE statement with WHERE clause you can remove specific record in a table.
SQL> DELETE FROM table_name
|Whereas TRUNCATE statement use for remove all record in a table.
||DELETE statement is a DML (Data Manipulation Language) command.
||Whereas TRUNCATE statement is a DDL (Data Definition Language) command.
||Executed DELETE statement you can UNDO the changes and return back to deleted data.
||Whereas executed TRUNCATE statement you can't return back.