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;
SQL> TRUNCATE TABLE emp_data; Table Truncated.
|1||If we want to delete all the records from the table, then our SQL query is:
|Whereas Truncate command is used to delete all records from the table. Our SQL query is:
|2||DELETE statement with WHERE clause you can remove specific record in a table.
|Whereas TRUNCATE statement use for remove all record in a table.|
|3||DELETE statement is a DML (Data Manipulation Language) command.||Whereas TRUNCATE statement is a DDL (Data Definition Language) command.|
|4||Executed DELETE statement you can UNDO the changes and return back to deleted data.||Whereas executed TRUNCATE statement you can't return back.|