Thought I’d add a small nugget of SQL knowledge I recently acquired to the blog; the difference between truncate table and delete from table. Both are ways to remove all the rows from a table, but which one should you use?
First off, a quick SQL language lesson. SQL statements are divided into two categories: the Data Definition Language (DDL) and the Data Manipulation Language (DML). DDL statements are used create and manipulate data structures. DML statements are used to modify data in those structures. DDL statements take place immediately. DML statements take place in memory first until they are committed to the database.
Truncate table is a DDL statement. Delete from table is a DML statement. This means that you must have ALTER permissions on the table if you want to use truncate.
The differences breakdown:
| Truncate Table | Delete From Table |
| Data Definition Language (DDL) Statement | Data Manipulation Language (DML) Statement |
| Truncate table writes to the log file only once. | Delete from writes to the log for every row. |
| Truncate table uses a table lock. | Delete from locks each row. |
| Truncate table resets the identity counter. | Delete from does not reset the identity counter. |
| You can’t truncate a table that is referenced by foreign keys. | You can use delete from on a table with foreign keys. |
| Truncate doesn’t activate triggers. | Delete from will activate triggers. |
If you know any more differences drop me a comment.