Difference Between Delete and Truncate in SQL
Difference Between Delete and Truncate in SQL
Truncate and Delete in SQL are two commands which are used to delete or remove rows from a table but they are not exactly similar in the way they operate.
The difference between Delete and Truncate command is the most basic concept in SQL yet it confuses many people. Maybe that's why it's also one of the most asked interview question.
To answer this question I'll go over both of the commands individually so that the difference is clear.
Delete command in SQL
DELETE is a DML (data manilulation language) command: Which means that the Delete command only deals with the data and data manipulation. It has no impact over the schema of a table.
Use of where clause: When we want to delete a particular row using delete command, we have use where clause, in which we provide a condition, if a row meets that condition only then it is deleted. When no where clause is used, all the rows get deleted.
Slower than Truncate: Delete command in SQL also maintains a log which results in slower execution as compared to Truncate command.
Deleting rows one at a time: Delete command removes rows one at a time and records an entry for the deleted rows in the transaction log.
Delete permission: To use delete command you will need delete permission on your table.
Indexed views: Delete command can be used on indexed views as well
Syntax of Delete Command in SQL
DELETE FROM emp WHERE firstName = 'Hitesh';
Truncate command in SQL
Truncate is a DDL (Data Definition language) Command: Which means that Truncate command impacts the structure or the schema of the table.
No where clause needed: Truncate command is not used with where clause and it removes all rows from a table.
Faster than Delete command: In case of Truncate command, there is minimal logging in the transaction log, thus Truncate command is faster as compared to delete command
Alter permission: To use truncate command, you will need Alter permission on your table
Indexed Views: Truncate command cannot be used with indexed views.
Less transaction space: Since there is minimal transaction log entries, truncate command uses less transaction space than delete command.
Rolling back: In sql server, you can rollback truncate command if you use transaction in your code.
Syntax of Truncate Command in SQL
TRUNCATE Table emp;
When to use Truncate command
When you want to completely reset the table including all the keys. Or when you you need to quickly clear out the table, in case you are working with test data.
Note: Truncate command locks the table, so it's not recommended to use it if the table is being shared between multiple users.
When to use delete command
It is recommended to use delete command in any situation except when where you absolutely need truncate. Also when delete command is used on a particular row, it locks that row and the remaining rows remain unlocked.
Difference Between Truncate and Delete Command in SQL
| Truncate | Delete |
|---|---|
| TRUNCATE removes all rows from a table. TRUNCATE is faster and doesn't use as much undo space as a DELETE.. | The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. |
| TRUNCATE is a DDL command so this command change structure of table | DELETE is a DML command. It only remove rows from a table, leaving the table structure untouched. |
| You cannot rollback in TRUNCAT. | In DELETE you can rollback. |
| In SQL, the auto increment counter gets reset with truncate | But not with delete. |