Sql: the difference between the truncate and delete

The SQL commands are similar and they have the same result: removing the records from the table. The question about the difference is often asked from freshers and in interviews as well. The difference is pretty simple but people confuse it because they see the same result of both queries.

The list of the differences are the next:

Type of Command: Truncate is a DDL (Data Definition Language) command and Delete is a DML (Data Manipulation Language) command.

RollBack: On DDL Commands, the changes made by them are committed automatically, while DML commands are not commited and can be rolled back. Some of the DDL commands are: SHOW TABLES, CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME, and some of the DML commands are: SELECT, INSERT, UPDATE, DELETE, MERGE – UPSERT, CALL, EXPLAIN PLAN, LOCK TABLE.

Table Structure: When you use Truncate command, all the rows in the table are deleted and the structure of the table is recreated, so does the indexes. When you use Delete command, only the desired rows (using the conditions with „where”) or all the rows are deleted and the structure remains unchanged.

Syntax: The syntax for commands is:

Truncate table <tablename> #command to truncate a table.

Delete from <tablename> #command to delete all the records from table.

Articles posted here: http://www.supravirtual.ro/stiri/Sql:_the_difference_between_the_truncate_and_delete