SQL TRUNCATE TABLE

SQL TRUNCATE TABLE command used to completely remove all table records. Not supporting to a WHERE clause.

SQL TRUNCATE command is faster and use some transaction log resources.

SQL TRUNCATE command logically equivalent to a DELETE command that deletes all rows, but they are practically different under some rules.

SQL TRUNCATE command 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.

Syntax

Considering following syntax that help you to understanding TRUNCATE,

TRUNCATE TABLE table_name;

Example

SQL> TRUNCATE TABLE emp_data;

Table Truncated.

Different between Delete and Truncate Commands

  DELETE TRUNCATE
1 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;
2 DELETE statement with WHERE clause you can remove specific record in a table. 
SQL> DELETE FROM table_name 
     WHERE condition;
 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.